cancel
Showing results for
Did you mean:  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

 Year1 500 Year2 1500 Year3 2000 Year4 100 Year5 800

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard...
98 REPLIES 98 Frequent Visitor

Good Morning.

I'm also having a similar problem in that the total for the measure i've created isn't adding up I have tried adding the "IsFiltered" and "SUMX"  functions within my measurement however so far haven't had success (the measurement does contain switch statements too) can you possibly advise? The DAX I'm using for the original measurement is below,

Measure = IF(MAX('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])="Major",
IF(COUNT('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])>1,13,
SWITCH(
DATEDIFF(max('Core Data'[Go Live Date]),max('Date Table'[Date]),WEEK)
, -12,1,-11,2,-10,3,-9,4,-8,5,-7,6,-6,8,-5,10,-4,10,-3,10,-2,11,-1,12,0,12,1,12,2,12,3,12,4,12,5,11,6,10,7,9,8,8,9,7,10,6,11,4,12,2)
)
,
IF(max('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])="Medium",
IF(COUNT('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])>1,13,
SWITCH(
DATEDIFF(max('Core Data'[Go Live Date]),max('Date Table'[Date]),WEEK)
, -12,0,-11,1,-10,2,-9,3,-8,4,-7,5,-6,7,-5,8,-4,8,-3,8,-2,9,-1,10,0,10,1,10,2,10,3,10,4,9,5,9,6,8,7,7,8,6,9,5,10,4,11,2,12,1))
,

IF(max('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])="Minor",
IF(COUNT('Core Data'[Change Impact Size (Major/Medium/Minor/Cosmetic])>1,13,
SWITCH(
DATEDIFF(max('Core Data'[Go Live Date]),max('Date Table'[Date]),WEEK)
, -12,0,-11,0,-10,0,-9,1,-8,2,-7,3,-6,4,-5,5,-4,6,-3,7,-2,7,-1,8,0,8,1,8,2,8,3,7,4,6,5,6,6,5,7,4,8,3,9,2,10,1,11,0,12,0))

))) Frequent Visitor

Hi there, Im having problem with the Grand Total for the Allocation Column. The idea is when the 2021 Allocation has no value to give me the average else if tere is value just give me that value. The formula is :

=IF(HASONEVALUE(Data[202109 Allocation Tons]),SUM(Data[4 Months Average]),SUM([202109 Allocation Tons]))

which provide the correct value at the cell level but the total is ot accurate.

Thanks for the help.  Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com  Responsive Resident Anyone have any advice on how I can get this to sum correctly?

Rows:

• Provider Name
• CPT Code

Columns

• DOS - Month

Values

• CPT Code (Count Distinct)  Super User

@jonnyA Have you looked at this? Measure Totals, The Final Word:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard...  Helper I

Hi

In my case its works only when i choose some dates or product filter but when i dont use any filter its not showing total values

check the screen shot.  measure is

Avg Sale lost (total ) = IF(HASONEFILTER('Product Movement'[Date]), IF([Total Qty Diff Avg] > 0, [Total Qty Diff Avg] * [Avg Selling Price (PV)]),SUMX(VALUES('Product Movement'[Date]), [Avg Sale Lost]) )  Super User

Hi,

In your visual, the Date field should actually be dragged from the Calendar Table.  I can help, if you explain the question and show the expected result.  Please also share the link from where i can download your PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com  Helper I

thanks i figure out the problem, thanks Frequent Visitor

Hi,

I have an issue with summarizing multiple measures. This is my Measure :
Somme des mesures = CALCULATE(

+ SUMX(VALUES('Calendar'[Indirect Manufacturing]),'Calendar'[Indirect Manufacturing])

+ SUMX(VALUES('Calendar'[Royalties]),'Calendar'[Royalties])

+ SUMX(VALUES('Calendar'[Bank position]),'Calendar'[Bank position]))

And this is my cumulative Measure :

Cumulative Total Measure = CALCULATE([Total measure 1],

FILTER(ALLSELECTED('Calendar'),'Calendar'[Week 3] <= MAX('Calendar'[Week 3]) && 'Calendar'[Week 3] >= DATE(2021,07,17))

)

It works properly for some rows but it stopped then : I don't understand what's wrong and why my measure stopped

Thank you so much Regular Visitor

I have a similar problem and can't get it to show the correct total.

I need to calculate Possible Turnover.

I have a measure calculating quantity on sales order - quantity on purchase order.

I have a table with all sales prices listed.

I created a measure:

SUMX('Sales Price Dim - SPD','Sales Price Dim - SPD'[Standard Price (LCY) - SPD]*[Available QTY (POT-SOT) - POT])

I have tried with the HASONEFILTER, but can't seem to succeed. Help 🙂 Frequent Visitor

I have a different issue with measures that I can't seem to figure out. I have a drop-down selector for a user to choose an Estimated Cost, I then refer to this in a measure like so: SelectedCostSELECTEDVALUE(PerEntryCost[EstimatedCost])

What I want to do next is divide this SelectedCost by the TotalEntries field in my grid below, I want to divide it by the 122 which is the total. Then when I have that value (which is 9.84) I want to use that 9.84 to multiply against the 92 in the first row and have my EstCost field show 905.28 and the second row multiply 9.84 by 30 to get 295.20. Then my final total of EstCost on the bottom row would show \$1,200.00.

Here are my measures:

SelectedCost = SELECTEDVALUE(PerEntryCost[EstimatedCost])

Total Entries = DISTINCTCOUNT(CHB_Declarations[CHBFileID])

PerEntryCost = [SelectedCost]/[Total Entries]

EstCost = [Total Entries]*[PerEntryCost]

As you can see below, the measure is being calculated at each row level not using the total of Total Entries, same goes for the PerEntryCost, I am expecting that to be 9.84 on each row. (I'm not going to be showing the per entry cost in the final solution, I just put it there to show the issue I am experiencing.) I really just need the EstCost to work the way I have described above. Some more deatil on this table view, this is based on a couple slicers of a date range and one customer, so if these filters are changed I need these measures to recalculate based on the new slicer selections.

I'm thinking this is probably pretty easy, I am just having a hard time figuring it out. I tried calculated columns and that didn't work either.  Thanks for any assistance!

Michael Giusto  Super User

Hi,

Edit the Total entries measure to:

Total Entries = calculate(DISTINCTCOUNT(CHB_Declarations[CHBFileID]),all(CHB_Declarations[broker_name]),all(CHB_Declarations[branch_name]))

Does this help?

Regards,
Ashish Mathur
http://www.ashishmathur.com Frequent Visitor

Ashish,

This worked but I had to make it a separate [Total Entires] measure as it affected my Total Entries in my grid and made both rows 122. I created another measure with your DAX and edited the PerEntryCost measure to that new measure and everything works great!  Thank you so much!  Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com Frequent Visitor

Ashish,

I don't see any option to mark your reply as Answer, I did give it the Thumbs Up, is that what you mean? Anonymous
Not applicable

Hi, My problem statement is a little different. Below is my table.

Name            forecast   capacity  Leaves               Availability                            Answer column

Abc                   33             37          0          if(37-33-0 < 0, 0, 37-33-0)                       4

efg                    42             37          8          if(37-42-8 < 0, 0, 37-42-8)                       0

Total to get                                                                                                                  4  Super User

Hi,

Do these measures work?

Availability inter = if([capacity]-[forecast]-[leaves]<0,0,[capacity]-[forecast]-[leaves])

Availability = SUMX(VALUES(Data[Name]),[Availabiliy inter])

Drag the Availability measure to your visual.  My assuption is that capacity, forecast and leaves are measures that you have already written.

Regards,
Ashish Mathur
http://www.ashishmathur.com New Member

Hi,

I'm strugging with the issue where I need to calculate one measure in a two ways based on the values.

The data model is much more complex, however to simplify it let's imagine that I have one column and two measures.If value in column is "D" pick value from second measure, otherwise first measure.

When I use below formula the row by row calulation is good however total is wrong.

Measureif = IF(SELECTEDVALUE('Table'[Column1])="D",[Measure2],[Measure1] )

I need to present all values in table and I cannot make columns instead of measures. I understand what SELECTEDVALUE is doing however don't know what other formula should I use n that case.

All suggestions are more than welcomed.

Thank you   Super User

The result of the Measureif measure is correct.  What problem are you facing?

Regards,
Ashish Mathur
http://www.ashishmathur.com New Member

Hello,

the total value is wrong.

bobby  Super User

Hi,

Try this measure

=SUMX(VALUES(Table[Column1]),[Measureif])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com Announcements #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference. Top Solution Authors
Top Kudoed Authors
Users online (2,211)