grand total in table visual is not summing values correctly

[lawadaa]

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

[ValtteriN]

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/

[lawadaa]

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

[Greg_Deckler]

@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