Average of distinct values
Answered
Hii,
I have problem.
I have a table of log times per user. the user is connect to a branch.
for every branch i calulated the distinct values of days that the users loged in divided by the number of working days in a month with gives me a number of usage for each branch. The grandTotal of the pivot table by avarge will give me the avarage of usage for all branch.
how can I calculate in one indicator the grandtotal in one function.
for example
branch logon days num of working days usage
AAA 10 25 0.4
BBB 5 25 0.2
CCC 20 25 0.8
avarage caculation is (0.4+0.8+0.2)/3 = 0.466
The problem is that the function SUM(count([days in logtime])) does not work.
Would love for some assistance.
Thanks,
Sivan ~~
-
Hi Sivan,
I'm not sure I fully understand your issue, do you have problems with grand total for last measure being calculated wrong, or do you want to replicate that calculation as a single indicator?
If you have issue with Grand Total, please check that grand total setting for last measure is set to auto and on the first two to sum. That should give you what you require.
In terms of calculating a single indicator, I would need to know how exactly you calculate first two measures before I could hint anything. I suspect that it might be better to carry those calculations to ElastiCube. Can you share those calculations?
Regards,
Michał
-
Another approach that I've used is to use the ALL() function in my calculation (see section "Calculating Contributions Using the ALL Function") in this article: https://documentation.sisense.com/creating-formulas-based-on-criteria-and-conditions-filters/
-
hi,
Thanks for the reply.
I want to calculate a single indicator the shows the avarage of usage ("days of use" divded by"num of days in month") for all of the branch in the organization.
the caculations for one branch is:
days of use = count([days in logtime]) - this part works just for one branch
num of days in month = [max of num_of_working_days] - this part works fine
the grandTotal was an example of the single indicator that i want to calculate.
the problem is when i expand the calculation of the indiactor for all the branches because the calculation just do distict on all the days od the month and not the avarage for every branch and the avarage all the results.
does it makes sense?
Sivan
-
One of the aggregate averaging functions may work for you. I have used it in a similar use case.
https://documentation.sisense.com/building-formulas-with-functions/
Average
Avg(<numeric Field>)
Calculates the mean average of the given values.
For example – AVG(Score) will calculate the mean average of the given scores.Avg(<group by field>, <aggregation>)
Calculates the average of the given aggregation grouped by another filed.
For example – Avg( Product, Total Sales) will calculates the average of the total sales per product.
Please sign in to leave a comment.
Comments
6 comments