Calculation fail with use of criteria on dimensions

Hi Anne-Mette

Just a quick suggesstion: What if you add ", 0" in the end of each syntax?

= if allcount(d1:0, 0, m1, 0) = allcount(d1:@"[Bjerringbro by]", 0, m1, 0) then ... 

As far as I know this will work as a "if fail then 0". Otherwise I think you need to show a bit more from your analysis to get an idea of your problem? 

Hi Louise

Thanks for your suggestion. However, it had no effect.

The value is an average income. I have created a user dimension with cities, where several cities are combined into one. Therefore, I have to divide the average income for the cities that are gathered by several.

As you can see below, the calculations are correct when not filtered. But if I choose a city via another object, which has the same user dimension, the calculation for all cities other than "Bjerringbro" fails.

Hi Anne-Mette,

It seems that this is coming from a Data Discovery cube...

Have you tried with the avg measure instead ... Gennemsnitsindkomst.avg. If it works, it might completely replace your calculation.

BR / Ole

Hi Ole

Yes it is coming from a Data Discovery cube - but the measure in the raw data is already an average. So the values will be incorrect if I use "Gennemsnitsindkomst.avg".

Hi Anne-Mette

I think I have a solution that will work - it requires a little work, but I think it will do the job.

Here's a table that's similar to yours - I've created a user dimension that contains groups of countries:

Now I will add the countries to the table (I will hide that data later) - but it's necessary to have the countries there to do the necessary calculations:

Now I will calculate (as a measure) how many countries are in each group:

allcount(all(s), 0, m1)

The (s) modifier means that only siblings (members of the same group) will be counted - the result looks like this:

Now I will do the total of each group by doing a calculation (as a measure) with a similar syntax:

sum(all(s),0,m1)

And then we divide the 2 previous calculations (again as a new measure)

sum(0,0,m3)/sum(0,0,m2)

Now comes the 2nd part - where we have to hide all the redundant stuff:

In visibility I uncheck the country labels, my original measure and the calculation "No of countries).

As this point my table looks like this:

All the numbers are there (just repeated too many times...) - let's make one last adjustment - a visibility agent with this condition:

allcount(d1:0(s), 0, m1) < allcount(all(s), 0, m1)

(the syntax says - count all siblings 1,2,3,4 etc - and if the number is smaller than the total number in the group, then hide it. This will leave just one member per group).

and this action:

Now we have the final result - and it's robust even if you drill like in your example.

Hope it makes sense :-)

Thank you very much, Niels Thomsen. It worked perfectly! :-)