Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello dears!
I have an app in which I want to create a pie chart with relative classification, in %, for a specific field.
My field called "_Essentiality" has binary classification (0/1) and I want to count my Key field (%CompoundKey) for each of these classifications, having percentage for each class, example:
_Essentiality | Relative Count |
0 | 97,3% |
1 | 2,7% |
But the problem is, when I try to use TOTAL expression in order to have my relative share, my TOTAL count doesn't match with the sum of Keys in each classification (see attached image):
As you can see, my count considering Dimension totals 49055 and my total count shows 49030, so when I try to create the A / B in order to have 0-100% sum, my "0" classification become, solely, more than 100%.
I've also tried to use ALL expression, without success.
Any ideas?
It looked that you are counting a key-field which often worked fine and personally I do it quite often. But remembering to quite old best practice guidance it shouldn't be done because not each side of the fact- and/or dimension-tables may have the same key-values (missing ones to all sides) and/or there might be any NULL's within them. If so the context of the view - object-dimensions, selections, advanced expressions and so on - could have an impact to the results.
Therefore you may try to enforce a certain view/relationship within the calculation, maybe by adding querying an table-unique field or something similar. More simple would be just to double the field within the target-table and then counting this.
The 'total' should be honouring the scope of the chart. My instinct is to check for a chart setting that could explain this, perhaps there is a 'dimension limit' in the chart? Or one of the dimensions has a 'hide nulls'. Something like that.
ALso, try creating a text box with just the expression placed in the text chart. If the value is 49055 you know its something specific about the pivot table chart.
It looked that you are counting a key-field which often worked fine and personally I do it quite often. But remembering to quite old best practice guidance it shouldn't be done because not each side of the fact- and/or dimension-tables may have the same key-values (missing ones to all sides) and/or there might be any NULL's within them. If so the context of the view - object-dimensions, selections, advanced expressions and so on - could have an impact to the results.
Therefore you may try to enforce a certain view/relationship within the calculation, maybe by adding querying an table-unique field or something similar. More simple would be just to double the field within the target-table and then counting this.
The text object try I've done it already. Shows 49030.
Regarding your comments on charts settings, I've done it all to show nulls, zeros, etc, etc. Nothing solved...
Hey Marcus, thanks for your answer!
Even not figuring out why exactly this happens, I was able to solve using your insight on key-field count.
I've changed to:
count ( {<[QAS Ellegible] = {1}>} [QAS Ellegible] )
And seems to work right now. The total count column shows 49055.
I thought that because I've created a script field [QAS Elligible] and put into my set analysis expression, I would not have problem with key-field count.
So, even not knowing exactly why this happened, problem solved. Thank you!!!