topic Re: grand total in table visual is not summing values correctly in DAX Commands and Tips

grand total in table visual is not summing values correctly

lawadaa — Sun, 22 Oct 2023 09:15:00 GMT

Hello,

i have a measure where i need to multiply a percentage column with another measure . in the row level the output is correct but tabel grand total is wrong

new achiev measure= [overall achiev]*MIN('Agent Rate Key'[Agent Rate])

i was trying to use summarize with the columns mentioned in the table but still it's not working

Re: grand total in table visual is not summing values correctly

Greg_Deckler — Sun, 22 Oct 2023 13:40:51 GMT

lawadaa First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: grand total in table visual is not summing values correctly

lawadaa — Sun, 22 Oct 2023 13:49:49 GMT

how can i use (Has one filter) funuction if i have multiple coulmns in my table?

Re: grand total in table visual is not summing values correctly

ValtteriN — Sun, 22 Oct 2023 13:53:16 GMT

Hi,

The root cause for this behavior is that typical dax functions don't do "visual level calculations" and instead the calculation is done on filter context. In practice this means e.g. this:

Data:

Measure:

Measure 4 = AVERAGE('Table (9)'[Value])

Now, one could think that this will result in Average of 20 since (10+30)/2 = 20. However here is the end result:

So (10 + 10 + 30) / 3 = 16,67

Measure resulting in expected end result could be e.g. this:

Measure 5 =

AVERAGEX(

SUMMARIZE('Table (9)','Table (9)'[Dimension],"Average",AVERAGE('Table (9)'[Value])),[Average])

This measure first creates aggregate table and then takes average of that table resulting in the expected 20.

In your example you can use SUMX instead of AVERAGEX. So first create calculated table within your measure. Ypu can consider placing it into variable like this:

Measure 5 =

var _vtable= SUMMARIZE('Table (9)','Table (9)'[Dimension],"Average",AVERAGE('Table (9)'[Value])) return

AVERAGEX(_vtable,[Average])

In your case replace the calculated column and expression matching your table and then use SUMX.

Here is a good article on the topic by sqlbi: Summing values for the total - SQLBI

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/