
1. Re: Show and calculate percentages in histogram for a calculated field
Michel Caissie Aug 7, 2018 2:55 PM (in response to Tapio SchmidtAchert)1 of 1 people found this helpfulTapio,
You can get the % with the following;
[Organization Count] / MIN({EXCLUDE [Organization]:COUNTD( [Organization] )} )
where
{EXCLUDE [Organization]:COUNTD( [Organization] )}
gives you the total number of organization in the view after filtering.
Michel

2. Re: Show and calculate percentages in histogram for a calculated field
Tapio SchmidtAchert Aug 9, 2018 2:12 AM (in response to Michel Caissie)Hi Michel,
thank you very much Michel Caissie that was the solution I was looking for. I got an error message because of not using the
Now i have a second problem: I would like to Filter on Organization Count, e.g. only values bigger than 1 and still want the result to be correct. I would have added a filter to context to achieve that, but as Organization count is a calcultated field I can't add it to context. Do you have an idea to achieve this?

3. Re: Show and calculate percentages in histogram for a calculated field
Michel Caissie Aug 15, 2018 10:03 AM (in response to Tapio SchmidtAchert)1 of 1 people found this helpfulTapio,
I haven't forgot you, I was kinda busy and I couldn't put the finger on a solution.
Currently the way you compute the organization count using the size() function gives you some limitations when you use it as a filter.
As you say, you cannot add it to context, but mostly, because of the order of operation , any other table calculation is computed before this
table calculation filter is applied. So even if you filter out the organization count lower than 2, the window_sum of organization would still
return 10 instead of 8.
So the goal here is to get the organization count without using any table calculations, and for this we need the Percent Bin to be a dimension.
We can get this by first computing the percent with the following lod calculation.
{FIXED [Organization],[Measure Number],DATETRUNC('quarter', [Reporting Period Start Date]) : SUM([Numerator])/(SUM([Denominator])SUM([Exceptions])SUM([Exclusions]))}
then we compute the Bin
IF [Percent (mc)]<=.8 Then "<80%"
ELSEIF [Percent (mc)]<=.85 Then "85%"
ELSEIF [Percent (mc)]<=.9 Then "90%"
ELSEIF [Percent (mc)]<=.95 Then "95%"
ELSEIF [Percent (mc)]<=1 Then "100%"
End
and we convert it to Dimension.
From there we can get the organization count with the following lod
{FIXED [Percent Bins (mc)]: COUNTD( [Organization] )}
So now by using the organization count as a filter, since it does not contain any table calculation , we can get the ratio with
MIN([Organization Count (mc)]) /WINDOW_SUM( MIN([Organization Count (mc)]))
compute Table Across
Now the WindowSum only gives the number of organization in the view.
note: the MIN could be replace by MAX or AVG but not SUM because it is a lod and you have the value for every row so you dont want to Sum those.
Make sure to add Measure Number and Quarter(Reporting...) to context. Since the Percent is now computing using a FIXED lod, we need
this calculation to compute after the filtering of those two dimensions. This will occur by adding them to context.
Michel

4. Re: Show and calculate percentages in histogram for a calculated field
Tapio SchmidtAchert Aug 20, 2018 1:58 AM (in response to Michel Caissie)Hi Michel,
thank you very much for your solution, I already applied it to my own data and it works like a charm.
I had the same idea to calculate without table calculation but did not know how to. Thank you very much for helping me out with this problem.
Tapio