Calculation fail with use of criteria on dimensions

• 

  Louise Stub Hansen

  July 02, 2021 05:47 (Edited July 02, 2021 05:48)

  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? 

  0
• 

  Anne-Mette Mortensen

  July 02, 2021 09:43

  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.

  

  

  0
• 

  Ole Dyring

  July 05, 2021 11:49

  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

  0
• 

  Anne-Mette Mortensen

  July 16, 2021 08:05

  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".

  0
• 

  Niels Thomsen

  August 09, 2021 12:55 (Edited August 09, 2021 12:59)

  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 :-)

   

  0
• 

  Anne-Mette Mortensen

  August 11, 2021 09:10

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

  0

Please sign in to leave a comment.