Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Greg_Deckler
Super User
Super User

Dealing with Measure Totals

This one has come up quite a bit recently. The issue surrounds using Measures in Table visualizations with a Total row. The complaint is that the "Total" row is "wrong" for the measure. Technically, the total row is correct for the measure, it's just not what most people expect. What people expect is for the "Total" to display the sum of the values in the column. Measures do not do this. Measures respect the context of the Total row and is calculated within that context. Therefore, a Measure used in a column in a table visualization will likely have an unexpected value in the Total column.

 

There are a couple ways of fixing this. The easiest is to turn off the Total row.

 

Assuming that is not what you want, you can use the HASONEFILTER function to get around this issue. However, the ultimate solution will depend on how your measure is calculated.

 

For example, given the following data:

 

Year Amount

Year1500
Year21500
Year32000
Year4100
Year5800

 

We wish to find the total extra Amount spent above 1000 for each year. If the amount is not over 1000, we wish to display 0. To this end, we create a measure:

 

MyMeasure = IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000) 

Adding this to a Table visualization along with Year, we get the correct answer for each of the rows, but the Total line displays 3900, not 1500 as we would expect. The figure 3900 is calculated because the Measure is performing its calculation for ALL of the rows in the table, so the calculation is (500 + 1500 + 2000 + 100 + 800) - 1000 = 3900.

 

Correct, but not what was expected.

 

To get around this problem, use HASONEFILTER to calculate the Measure one way within a row context and another way within the Total row context, such as:

 

MyMeasure2 = IF(HASONEFILTER(Table[Year]),
IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000),
SUMX(FILTER(Table,[Amount]>1000),[Amount]-1000)
)

Breaking this down, we essentially wrap our original measure in an IF statement that has the HASONEFILTER function as the logical test. If HASONEFILTER equals true, we calculate our Measure as before. However, if HASONEFILTER is false, we know that we have a Total row and we calculate our Measure a different way.

 

 

 

 

 

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
104 REPLIES 104
sivashankr
Helper II
Helper II

Can you able to help us identify and fix the issue for this  formula ?

showing the wrong summation for the total in the below calucation

ValueWeightedAverage =
VAR
NetValTemp= CALCULATE(LASTNONBLANK(InvoicesAll[ITEMNETVALUE],1))
VAR
Netval = CALCULATE(sum(InvoicesAll[ITEMNETVALUE]))
VAR
Days=[_SCdateDiff]
VAR
VWA=DIVIDE(NetVal,[_CustCurrTot],0)
RETURN
VWA

power bipower biexcel manaul- required this valueexcel manaul- required this value

pbix file:

https://onedrive.live.com/?authkey=%21AMrOI0O9c72Xs%2Dw&cid=C007331E59E3588D&id=C007331E59E3588D%211...

DiegoSDEOAV
Regular Visitor

Hi,

 

I have an issue with the total, I had two columns, the first one its the value and the second one takes that value and multiplies it aoccording his % of participation, in the first colum the total works great, but for the second column its taking the average of the % of participaction and then sum all the values. 

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Value_unit  = IF(SELECTEDVALUE('Unit'[Value])="One",sum(Table1[Detail.qty]),if(SELECTEDVALUE('Unit'[Value])="Two",(sum(Table1[Detail.qty]) * SUM('CG'[RET_Size] )),IF(SELECTEDVALUE('Unit'[Value])="Three",(sum(Table1[Detail.qty]) * SUM('CG'[RET_Size]) * SUM('CG'[RET_One]))/10)))

 

could any one please modify the above measure to get correct subtotals by using hasonevalue or hasonefilter or any other..

Anonymous
Not applicable

share dataset and your expected result

Anonymous
Not applicable

expected outputexpected output

Anonymous
Not applicable

i am confused with your requirement. I suggest you to do calculation using SUMX and Values

Anonymous
Not applicable
Anonymous
Not applicable

Hi,

 

Im not sure if this is the right subject to be asking this but Im in desperate need of a solution. 

I have an issue with the totalling of my Matrix, for some reason it is not calculating it correctly. I have tried using the sumx function but still no luck.  If I calculate the values for each row I get to 55 but on the matrix it is 53.  Please assist.incorrect totals.png

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur , do you want me to share the file with you or just screenshots?

The PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur,

 

I wouldnt be able to share the file due to client confidential information. I think the issue is in my data model for some reason. I have two fact tables so I had to make use of the userrelationship function, doing testing now to see if that might be the reason causing this mis calculation  

Anonymous
Not applicable

option 1 vertical.PNG

 

i got the same issue, i used the has one filter.

 

average holding period= IF(  HASONEFILTER(Capex[Fully E. N. Leases & A. Year]),   IF(FIRSTNONBLANK(Capex[Fully E. N. Leases & A. Year],TRUE()) = YEAR(TODAY()), MONTH(TODAY()),  IF(LASTNONBLANK(Capex[Fully E. N. Leases & A. Year], TRUE())  = LASTNONBLANK(Capex[Built Date],TRUE()) ,     12 - VALUE(month(average(Capex[OTHVR Actual Date])))   ,    VALUE(12  ))) ,      SUMX(Capex,12 - VALUE(month(average(Capex[OTHVR Actual Date]))))             )

vmakhija
Post Prodigy
Post Prodigy

@Greg_Deckler

This is helpful...

Any way to make it scalable such that when users use or dont use a particular field in the matrix table or add multiple fields to the matrix, the measure should be scalable to calculate totals accordingly?

I mean I am trying to avoid writing HASONEVALUE for multiple fields which will not be anyway feasible if user has self service capabilities.

Any suggestions?

 

Regards

There is a new function ISINSCOPE that is supposed to be a "better" way to do this but I think it still suffers from the same issues that you don't like.

 

https://docs.microsoft.com/en-us/dax/isinscope-function-dax

 

It's unfortunate but measure totals are just something that end users need to be aware of, there's no foolproof way of keeping them from shooting themselve in the foot. And I am not aware of something like an "ISALL" function that would return TRUE if everything was selected.

 

I posted an Idea for this: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36240835-isall-or-istotal-dax-fun...

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler

Thanks for taking time for this.

I was nearly certain there isn't a way to cater this.

Anyway, I will talk to my users on what are the limitations and discuss on ways they can take care of those.

 

And thanks for posting an idea for the same. I voted for it.

 

Regards

joydeep7
Frequent Visitor

Thanks Greg, you saved the day 🙂

@joydeep7, Awesome! Glad to hear it. I also just posted Measure Totals, The Final Word, that provides a general solution to the problem.

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Capture.PNG

Hi my measure totals are not summing up properly, is there a way to use this if and else function with two measures to get the sum correctly?

Greg,

 

ANy thoughts How I can modify my dax expression to fix my totals issue I'm have in a Datagrid.. If I create a measure for each Calculate statment separately, works fine, but I need relect in one measure for the Grid.

 

It seems like the total in reflecting amounts for all conditions together.

 

On adds to another level of complexity is that the column MTD_PY_Actuals is a prior Year calc., thoughts?

 

TD_PY_Variance_Matrix =
(
CALCULATE (
KPI_Finance_Matrix[MTD_Actuals_Matrix] - KPI_Finance_Matrix[MTD_PY_Actuals],
KPI_Finance_Matrix[Group] = "Revenue"
)
)
+ (
CALCULATE (
KPI_Finance_Matrix[MTD_Actuals_Matrix] - KPI_Finance_Matrix[MTD_PY_Actuals],
KPI_Finance_Matrix[Group] = "Gross Margin"
)
)
+ (
CALCULATE (
KPI_Finance_Matrix[MTD_PY_Actuals] - KPI_Finance_Matrix[MTD_Actuals_Matrix],
KPI_Finance_Matrix[Group] = "Operating Expenses"
)
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors