DAX Calculation showing incorrect result for Totals in Smart Narrative Visual

[mkasinathan1]

Hi Team,

We are trying to evaluate Totals for simple data set using DAX Expression and show them in Tooltips for our visuals. The DAX Seems to be giving correct result while looking at default PBI tooltip. But while looking at Smart Narrative Tooltip, the totals are returning different numbers for Multi-selection in Filters.

DataSet:

Grade	Dept	Value
A	100	1
B	100	4
B	101	1
C	100	2
C	101	4
D	100	1
D	101	1
E	100	5
A	102	2
B	102	3
C	102	4
D	102	5
E	102	6

DAX Expression to Calculate Totals: 

Totals = CALCULATE(SUM(Sheet1[Value]), REMOVEFILTERS(Sheet1[Grade]))

The Grade totals must be returning value 19. It is giving correct value 19 from Direct table or default tooltip. However from Smart Narrative page(Tooltip), it is giving wrong result as shown in image. This is because Grades- A & E are not part of Department(Filter) and hence those records are not getting included in Totals. It looks like DAX Calculation is getting executed separately for each filter selection- 100 and then 101 and then Totals is evaluated.

How to bring the same totals for all Grade even though a respective grade is missed out for certain departments?

Thanks,

Muthu

[mkasinathan1]

Hi Amit,
We have a slicer to be considered in the calculation. The above calculation will remove all filters from the context.  Please help me with revised logic that considers selected Filters/slicers and display totals only based on selection.

[tamerj1]

Hi @mkasinathan1 
This happens due to Auto-Exist Feature in Power Bi. You can find many artiles and videos about it. Here is one for your reference if you are aintersted AutoExist and Normalization - SQLBI

Long story short, use a dimention table to slice by Department. Please follow the following steps and arrached sample file. 

[mkasinathan1]

Hi,
Is it possible to handle the issue using DAX expression instead of making changes to data model?

The reason i'm asking you this is because, we have plenty of slicers in our real time reports and we are in the verge of moving changes to Production. Making changes to data model at this phase would be difficult for us to re-validate all metrics/data points now.

Appreciate if you could help me with your expertise on this.

[tamerj1]

@mkasinathan1 

Adding a disconnected local single column table to your data model is not considered as "changing the data model". Changing the column in each slicer will take 5 seconds per slicer. Even if there is another way around it could be complex and you never know when and where other errors will appear. 

[Anonymous]

Hi,


My initial thought is: would it work if you changed your totals calculation to be using some form of the "count" formula instead? 

[mkasinathan1]

Hi,
When you mean some counts, can you please help me with the formula?

[amitchandak]

@mkasinathan1 , Try this in tooltip page

Totals = CALCULATE(SUM(Sheet1[Value]), REMOVEFILTERS(Sheet1))