- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Power BI forums
- Galleries
- Quick Measures Gallery
- Re: Measure Totals, The Final Word

06-14-2023 06:40 AM - last edited 06-14-2023 09:59 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Measure Totals, The Final Word

10-21-2018
06:09 AM

With apologies to Theodor Geisel...

*Measure totals have you perturbed?**Fear not!**It's Measure Totals, The Final Word,*

*These measures work with matrices,**They work with tables,**They work with rows and columns and labels.*

*They work in the daytime,**They work at night,**They work to make sure the totals are right!*

*Now that you've seen them,**Now that you've heard,**Shout it out loud, it's Measure Totals, The Final Word!*

At some point, we've all been frustrated by measure totals. If you want to understand why, read this post.

The technique employed here is fairly simple and should work in all "standard" cases of where you just want the Total line to, well, display the total (sum) of a measure. For more complex scenarios, see my Matrix Measure Total Triple Threat Rock & Roll measure.

Essentially, create a measure, any measure, that performs your desired calculation and returns the correct result at the row level. This becomes your "m_Single" measure. Now, create an "m_Total" measure that performs a SUMMARIZE of your data, exactly as how it is displayed in your table or matrix and use the "m_Single" measure within that SUMMARIZE statement to provide the values for the individually summarized rows. Finally, perform a SUMX across that summarized table. The measures presented in this PBIX file also do a HASONEVALUE check that isn't really necessary in most cases but perhaps lends a little confidence to the user that the SUMX is only employed in the Total line and might also add some performance improvements.

In effect, you are recreating the displayed visualization in memory as a table and then doing a summation across that table for the total line, as you would intuitively expect a total line in a table or matrix to work.

So, if we have a measure like:

m_Single = SUM(Table1[Value])-50

This measure will cause problems in total lines. So, if we are summarizing by [Name], we create this measure:

m_Total 1 = VAR __table = SUMMARIZE('Table1',[Name],"__value",[m_Single]) RETURN IF(HASONEVALUE(Table1[Name]),[m_Single],SUMX(__table,[__value]))

If we are summarizing by [Category1], we create this measure:

m_Total 2 = VAR __table = SUMMARIZE('Table1',[Category1],"__value",[m_Single]) RETURN IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value]))

And so on...

We use these "m_Total" measures in our visualizations. The "m_Single" measure is still used, but not directly in the visuals themselves.

Is it annoying to have to create multiple measures and specifically tailor them to each individual visual? Yes, yes it is.

eyJrIjoiODBmNmI4YjItZTMwYi00ZDU4LTg0MWItMzYyZWU3ODk4ZWI4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-20-2024
07:31 PM

Hey, I've tried this method and it worked for two columns. The totals for Joined Adjust and Offered Adjust are now correct.

However, if you sum up the rows in the Offer Adjust TOTAL, it's not adding up the values "3" and "2" (highlighted in yellow) in the total. The total should be 41, not 36.

I used the same formula as the ones used in the Joined Adjust Total and Offered Adjust column.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-21-2024
12:12 PM

HI,

I have the following example of a situation in a Power BI matrix:

| MONTH 1 | MONTH 2 | TOTAL |

VALUES | 5 | 2 | 2,85 |

See that the result of my DAX formula in month 1 is 5 and in month 2 is 2.

So the sum should be 7 (TOTAL), but is bringing 2.85.

This is because my DAX formula has sum, multiplication and division. Because of this when I join the 2 months together, bring me a result with total considering the DAX formula acumulating the 2 months.

For Example:

Month 1

(3 + 7) / 2 = 5

Month 2

(6 + 4) / 5 = 2

Month 1 and month 2 together the total is considered the sums of dividends by the sum of the divisors of the two months together to bring 2.85 as a result as follows:

(3 + 7 + 6 + 4) / (2 + 5) = 2,85

Is there any way for the total column that is already automatic from Power BI as shown below, to sum the columns and bring the value 7 instead of 2.85?

The real matrix is like that, where the sum of "Valor Realizado of January" + "Valor Realizado of February" = 9.276,23.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-14-2024
06:23 AM

I don´t know how to apply this to my issue, please can someone help me?

Re: Adjust the measure to also calculate the sum f... - Microsoft Fabric Community

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-05-2024
12:43 AM

@Greg_Deckler what if we use stand alone table as field parameter in palce of Category in visual , i tried with field parameter in measure it is giving composite key error

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-30-2023
06:51 PM

HI @Greg_Deckler nice post!

One question, in case if I am using a Matriz table with level 1 being 'Name' and level 2 being "Category1' , as mentioned in your example, how can I have a measure to calculate the correct values in both levels? the m_Total 1 measure will give the right infomration for the 'Name' level, but if I drill down to Category1, it will mess up again... it;s really anoing...thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-12-2023
04:51 AM

Hi, I've noticed that all my measures are doing the same with totals.

I've also tried to rewrite them as additive but it doesn't fix the issue. Could you help me with these?

Thank you!

VAR currentCustomers = VALUES('_Combined'[Customer name])

VAR currentDate = MAX('*DATE TABLE'[Date])

VAR prevYear = YEAR(currentDate) - 1

VAR pastCustomers =

CALCULATETABLE(

VALUES('_Combined'[Customer name]),

ALL('_Combined'),

YEAR('*DATE TABLE'[Date]) = prevYear,

'*DATE TABLE'[Date] < DATE(YEAR(currentDate), 1, 1)

)

VAR newCustomers = EXCEPT(currentCustomers, pastCustomers)

RETURN

COUNTROWS(newCustomers)

-----

VAR currentCustomers = VALUES('_Combined'[Customer name])

VAR currentDate = MAX('*DATE TABLE'[Date])

VAR pastCustomers = CALCULATETABLE(

VALUES('_Combined'[Customer name]),

ALL('_Combined'),

YEAR('*DATE TABLE'[Date]) < YEAR(currentDate)

)

VAR newCustomers = EXCEPT(currentCustomers, pastCustomers)

RETURN

CALCULATE(

SUM('_Combined'[Amount EUR]),

FILTER(

'_Combined',

'_Combined'[Customer name] IN newCustomers

)

)

-----

VAR StartDate2018 = DATE(2018, 1, 1)

VAR StartDate2019 = DATE(2019, 1, 1)

VAR CurrentDate = MAX('*DATE TABLE'[Date])

RETURN

IF(

CurrentDate >= StartDate2018 && CurrentDate < StartDate2019,

CALCULATE(

[Total Sales],

DATESBETWEEN(

'*DATE TABLE'[Date],

StartDate2018,

CurrentDate

)

),

IF(

CurrentDate >= StartDate2019,

CALCULATE(

[Total Sales],

DATESINPERIOD(

'*DATE TABLE'[Date],

MAX(StartDate2019, CurrentDate),

-12,

MONTH

)

)

)

)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-12-2023
08:54 AM

@mbacicci So, it depends on how you are using these in a visual. If those are your base measures and they work at the row level, then for the first one for example, if you are summarizing this measure in table based on year it would look something like:

```
New Customers Year TOTAL =
VAR __Table = SUMMARIZE( '*DATE TABLE', [Year], "__NewCustomers", [New Customers Year] )
VAR __Result = IF( HASONEVALUE( '*DATE TABLE'[Year] ), [New Customers Year], SUMX( __Table, [__NewCustomers])
RETURN
__Result
```

But, if you are summarizing in your table/matrix visual by year and month, the solution will be slightly different. So, really need the details to provide a specific solution. So, in this case, you would just use the new "TOTAL" version of the measure since it would work in both row and total lines and you could hide the other measure.

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-10-2023
01:51 AM

```
its retrun wrong rwo Total how to solve ?
TotalSales =
VAR Sales2022H1 = SUM('RecieptDetials 2022 H1'[Net amount])
VAR Sales2022H2 = SUM('RecieptDetials 2022 H2'[Net amount])
VAR Sales2023H1 = SUM('RecieptDetials 2023 H1'[Net amount])
VAR Sales2023H2 = SUM('RecieptDetials 2023 H2'[Net amount])
RETURN
CALCULATE(
Sales2022H1 + Sales2022H2 + Sales2023H1 + Sales2023H2
)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-06-2023
10:24 PM

Hi @Greg_Deckler , thanks for all your postings! I tried the below in the attached project (page 2).

m_Total 2 = VAR __table = SUMMARIZE('Table1',[Category1],"__value",[m_Single]) RETURN IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value]))

My table is DailyBalance, grouping by Date, summing the value LatestValue. Shouldn't my "Test" column be showing the total of LatestValue by day instead of the same value as LatestValue?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-25-2023
08:57 AM

This looks like something I could use - but I'm a newbie Power BI analyst. If I have a matrix with multiple levels, how would I apply this to the subtotals? See screenshot I have a measure that determines if the lineitem should be displaying a max or a sum - but the subtotal is totaling everything, not just the max it displays

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-25-2023
08:14 PM

Hi, the issue of totals when using functions other than sum has been around for a while , and is basically due to the different views on what to do with the total in such instances:

should it be:

1.a max of all the single lines ie returns a value you will already have in on of your rows. (the default behaviour)

2. a sum of all the previous max values shown in rows (i think the desired behaviour a lot want to see)

The simplist way in a simple two tier matrix as shown to deal with it is the use of the Sumx function with the Max being done at each row level., the table referred to is the fields in the matrix. A very good explanation of how to do this is https://youtu.be/yw0QHu9V4UQ?si=YqMmQV7zMNvvjH1-

You can also use the inscope function to drive a switch function , where you basically test if you are on a total line ,see this vid https://youtu.be/1tdufLz7_0U?si=9Amxsr2x4zCSAAv5

but yes not as easy as it should be..

Enjoy

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-25-2023
09:19 AM

@stitch_es Well it definitely depends on the circumstance but the general solution is to use SUMMARIZE and as the groupings, include the Rows in your matrix but the grouping will be different depending on where you are in your hierarchy. This additional article should help with that:

Matrix Measure Total Triple Threat Rock & Roll - Microsoft Fabric Community

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-25-2023
01:59 PM

Thank you for responding so quickly @Greg . Maybe you can direct me in where I'm going wrong? I have a measure that determines if the row should show a sum of line items or a max- that's working (max vs sum), and then i created a measure using your suggestion here for the totals to take that max and use that to sum it up - but it doesn't seem to be working, it just sums up as if the max is not part of the equation. See screenshots for explanation.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-25-2023
03:37 PM

@stitch_es Hard to know for sure without sample data to play with but it seems like you are not summarizing correctly. I would think you would need at least 2 groupings, ParentCategory and LineItemName.

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-14-2023
06:40 AM

Hi @Greg_Deckler ,

Can you please help me understand why Totals are wrong in a Matrix below .

I am attaching .pbix file also

In above matrix i am spreading value of each NewVal column till the date of Next NewValue,I got the expected spread results

But ......Totals are Wrong.....

Below is The measure

Spread Value =

Var val= CALCULATE(count(Data[NewValue]),ALLEXCEPT(DimDate,DimDate[Date]))

var MinDate =CALCULATE(MIN(Data[CreatedDate]),

ALLEXCEPT(Data,Data[ProjectName],Data[Index]))

var MaxDate =CALCULATE(MAX(Data[EndDateFinal]),

ALLEXCEPT(Data,Data[ProjectName],Data[Index]))

return

IF(HASONEVALUE(Data[CreatedDate]),COUNTX(KEEPFILTERS(Data),val),

IF(HASONEVALUE(DimDate[Date])=FALSE() && max(DimDate[Date])>MaxDate,BLANK(),

IF(HASONEVALUE(DimDate[Date])=FALSE() && MIN(DimDate[Date])>MinDate,Val

)))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
05:12 AM

Hi @Greg_Deckler ,

This is awesome and I think would solve my problem that I am having but I am new to writing dax and the forumla I am trying to configure is already very.... busy. Could you take a look and help guide me toward a solution that would work?

RVU % = IF(and(sum(Summary[25th % tile PTD])=0,sum(Summary[wRVU PTD])=0),0,IF(sum(Summary[25th % tile PTD])=0,0,IF(sum(Summary[wRVU PTD])<=sum(Summary[25th % tile PTD]),sum(Summary[wRVU PTD])/sum(Summary[25th % tile PTD])*0.25,IF(sum(Summary[wRVU PTD])<=sum(Summary[50th % tile PTD]),((sum(Summary[wRVU PTD])-sum(Summary[50th % tile PTD]))/(sum(Summary[50th % tile PTD])-sum(Summary[25th % tile PTD]))*0.25)+0.25,IF(sum(Summary[wRVU PTD])<=sum(Summary[75th % tile PTD]),((sum(Summary[wRVU PTD])-sum(Summary[50th % tile PTD]))/(sum(Summary[75th % tile PTD])-sum(Summary[50th % tile PTD]))*0.25)+0.5,IF(((sum(Summary[wRVU PTD])-sum(Summary[75th % tile PTD]))/(sum(Summary[90th % tile PTD])-sum(Summary[75th % tile PTD]))*0.15)+0.75>0.99,(((sum(Summary[wRVU PTD])-sum(Summary[75th % tile PTD]))/(sum(Summary[90th % tile PTD])-sum(Summary[75th % tile PTD]))*0.15)+0.75),((sum(Summary[wRVU PTD])-sum(Summary[75th % tile PTD]))/(sum(Summary[90th % tile PTD])-sum(Summary[75th % tile PTD]))*0.15)+0.75))))))

It calculates correctly on the row level but same issue as you stated the totals are very messed up and they are meant to be a percentage. Any guidance, insight or thoughts?

Thanks so much in advance,

Candace

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-26-2022
10:12 PM

@Greg_Deckler I feel like Salieri reading Mozart's sheet music. I regret that I have but one kudos to give for my... Deckler? This is some powerful dark magic you got here. Thanks for sharing.

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-03-2022
12:44 AM

Hi @Greg_Deckler,

I have this table:

In the second column i did the MAX function and I was trying to get the sum of that column

I tried what you said:

m_Total =

VAR __table = SUMMARIZE('Documents',[No-QR7],"__value",[max nb pages])

RETURN

IF(HASONEVALUE(Documents[No-QR7]),[max nb pages],SUMX(__table,[__value]))

but it doesn't give the correct sum, it supposed to be 1034 but it gives 964 (it looks like it stops adding after the 4 9s.)

Can you help me please?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-03-2022
04:28 PM

@Anonymous My guess is that it is perhaps removing duplicates. For example, I see in your image that you have 13-7965/7966/7967 listed twice so do you want that counted twice? If you use SUMMARIZE, that returns distinct values and then if you use MAX you would only get one 6, not two. Otherwise, you would want to use CONCATENATEX on your __table variable in your return statement to get a list of what all is in that table so that you can see what is going on.

Follow on LinkedIn

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-04-2021
12:54 PM

Hello! @Greg_Deckler I have a slighty different issue I have the following:

I´m trying to recreate a forecast based on YTD actual values and budget to complete, so I have a couple of tables:

Fact tables:

Presupuesto "Budget"

contabilidad Balance_Prueba "actual"

Ajustes Forecast "forecast Adjusments"

'Ajustes EEFF' "Financial Adjustments"

So, what I´m tryign to do is, that if there is not an "actual" well get the forecast.... on this "m_single" measure

And then I did the "m:total" version

However, when I try them on a table..... it get this result

Nor the single o the total are summarizing correctly.

can you help me?