TOTAL expression not counting correctly

[paulinhok14]

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?

[JonnyPoole]

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.

[marcus_sommer · Accepted solution]

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.

[paulinhok14]

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

[paulinhok14]

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!!!