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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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.

 

 

 

 

 

 

 

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
108 REPLIES 108
GerardVall
Frequent Visitor

Hello, I'm dealing with a similar isue.

 

I have a Measurement that includes a LookUpValue to change a percentage value on the measurement depending on a date. 

 

This part of the formula has to be excluded in the total row but i haven't been able to get it work.

 

To understand what i'm trying to do:

I need a measurement to calculate the objective of sales on my company. The objective is 20% higher of the total of sales in the last year but its distributed diferently by months.

  1. January: 8% of that 20%
  2. February: 9% of that 20%
  3. March: 6% of that 20%
  4. etc....

 

If someone could help i would apreciate it so much.

 

 

Thanks

Hi,

Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.


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

Hi,

 

Here is the example with data:

2023 Sales:12000000
2024 Objective:14400000

 

Month% of 20%Sales per month 2023Desired ValuesFormula
January0,07100000011680002023Sales*0.2*0.07+SalesMonth
February0,08100000011920002023Sales*0.2*0.08+SalesMonth
March0,08100000011920002023Sales*0.2*0.08+SalesMonth
April0,08100000011920002023Sales*0.2*0.08+SalesMonth
May0,09100000012160002023Sales*0.2*0.09+SalesMonth
June0,09100000012160002023Sales*0.2*0.09+SalesMonth
July0,09100000012160002023Sales*0.2*0.09+SalesMonth
August0,06100000011440002023Sales*0.2*0.06+SalesMonth
September0,09100000012160002023Sales*0.2*0.09+SalesMonth
October0,09100000012160002023Sales*0.2*0.09+SalesMonth
November0,09100000012160002023Sales*0.2*0.09+SalesMonth
December0,09100000012160002023Sales*0.2*0.09+SalesMonth
TOTAL  144000002023Sales*0.2+2023Sales

 

Sales are calculated with a SUM in another table.

 

Thanks,

I do not understand your question.  Someone who does will help.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Bunmz_a
Frequent Visitor

Please I am experiencing the same issue

 

My objective is to calculate the total budget associated with each Workorder status however for Projected Workorders, the budget for Open and Initiated should be netted off till it gets to 0. Meaning the Budget measure would need to incorporate an adjustment for Projected Workorders

 

I have been able to do this successfully but the grand total does not work.

 

For the grand total, I am still getting a sum that reflect the original not adjusted projected budget regardless of the logic built into the measure

 

Below is my measure. When I use ‘HASONEVALUE’ I get 0 as the grand total

 

Please help

 

 

Budget =

VAR WOstatus =

    FIRSTNONBLANK ( 'Projected WO'[WO_Status], 1 )

 

VAR BudgetTotal =

    CALCULATE (

        SUM ( Budget[Amount] ),

        FILTER ( Budget, Budget[BudgetType] = "Budget" )

    )

   

VAR ProjectedAmount =

    CALCULATE (

        BudgetTotal,

        'Projected WO'[WO_Status] IN { "Projected", "projected" }

    )

 

VAR InitiatedAmount =

    CALCULATE (

        BudgetTotal,

        'Projected WO'[WO_Status] IN { "Initiated", "initiated" }

    )

 

VAR OpenAmount =

    CALCULATE (

        BudgetTotal,

        'Projected WO'[WO_Status] IN { "open", "Open" }

    )

 

VAR AdjustedProjectedAmount = MAX( (ProjectedAmount - InitiatedAmount - OpenAmount ), 0)

 

RETURN

    IF (

        NOT ( WOstatus IN { "Projected", "projected" } ),

        BudgetTotal,

        AdjustedProjectedAmount)

Hi,

Share data in a format that can be pasted in an MS Excel file.  Explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
cheid_4838
Helper III
Helper III

Due to how the data is structured I had to create columns to break out count versus the dollar amounts associated with each itemcode in the dataset  Each itemcode has a column for quantity and charge.  All I had to do was drag each column into the values field in the matrix to get everything to total up.  Do the totals calcualted or averages matter if the totals come from a measure versus a column?

cheid_4838_1-1708614511218.png

 

 

 

I guess I am trying to determine how this applies to me if the column totals are accurate since they are coming from column totals not measures.  I under how the averages will not be correct if calculating a measure, but shouldn't the averages be accurate since the totals come from columns and not measures?  Thanks for your help.

@cheid_4838 It certainly does matter. Column totals always work. Measure totals, it depends. Here is actually a better article 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



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
jonnyA
Responsive Resident
Responsive Resident

Thx for the input!  I'm from from an expert at putting together formula's.  Can you provide a sample given the formula i sent in my original message?

 

Thanks

Brad_Roberts
Frequent Visitor

Greg,

I'm brand new to Power BI and I'm attempting to apply your solution.  My row dax works fine but the total is not working.  Change SF is a measure of (Sum of All Area - Start SF) and I want to split that between two new measures (Move-In and Move-Out).

 

Brad_Roberts_0-1670441670754.png

Any help is appreciated.

 

Thanks,

Tony

 

 

rmckenna594
New Member

Hi, 

I am having some difficulties with linking two separate data tables with a CALCULATE(SUM) calculated column. I want to bring in the total production cost at item level using the calculated column, but then apply a formula on this to generate the cost per unit in stock.

 

I have tried to do this using a calculated measure but the Total of the Measure does not equate to the sum of all of my table row values.

Urgent help on this would be greatly appreciated 🙂

 

Regards,

Ryan

Patrick95
Frequent Visitor

Hi,

 

I've created a measure to calculate the distinct "Document No." when they have a certain value (FILTER ON: '42-CHECK-SERIALNO'). 

It could be that the same Document No. will have the value (42-...) on a different date. 

 

The totals are corrrect in the matrix, but the subtotals will count the value even if the Document No. has already hit the value (42-CHECK-SERIALNO) already on a different date. Herefor if you count the subtotals, you will get a different total als the one that is listed correctly in the matrix.

 

If the "Document No." occurs several times on different dates by value (42-...), then I do not want to count this (double) in the subtotals. It should only count the first time the "Document No." hits the status (42-...)

 

Patrick95_0-1661518906275.png

 

 

Patrick95_1-1661518906292.png

 

 

I hope someone can help me with this issue

 

Hi,

What is the problem there.  The subtotal shows 34.  Are you expecting a different result there?


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

Hi Ashish,

 

Thank you for replying! 

 

As you can see, the total is 34 which is correct. If you add up the subtotals (10+17+12+9 = 48), you get a different result. The addition of the subtotals is incorrect The subtotals will count the value even if the same Document No. has the value (42-CHECK-SERIALNO) on a different date.

 

Therefor, I would like to create a measure: If the "Document No." occurs several times on different dates by value (42-...), then I do not want to count this (double) in the subtotals. It should only count the first time the "Document No." hits the status (42-...).

 

Unfortunately, I cannot figure out how to do this.

 

Do you have experience how to do this?

 

Thanks in advance.

 

Patrick

Hi,

Share the link from where i can download your PBI file.


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

It is shocking that after a number of years Power BI still does not offer an option to show the sum of the above columns as the total. Outside of a table - e.g. KPI - this "solution" breaks. This is disappointing.

100% agree. Ridiculous that a BI tool would behave this way. 

Anonymous
Not applicable

It's so easy to do in Tableau.

I'm totally agréé with you. In a visual table or others, the total row should have to be correct anyway the formula measure. Like in Excel with pivot table for instance. I would like PBI to correct this point to focus my reflection on the row ans not on the total.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors