- Power BI forums
- Updates
- News & Announcements
- 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
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- 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 Engagement
- T-Shirt Design Challenge 2023
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

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

Showing results for

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

- 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

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

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.

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.

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?

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

05-20-2022
05:52 AM

Hi,

Was just wondering if you have found a solution to this.? I too have a forecasting measure and cannot get the correct sum.

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

08-04-2021
06:05 AM

Hi,

I have an issue with summarizing multiple measures. This is my Measure :

Somme des mesures = CALCULATE(

SUMX(VALUES('Calendar'[Add on]),'Calendar'[Add on])

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

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

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

+ 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 :

Hi,

I have an issue with summarizing multiple measures. This is my Measure :

Somme des mesures = CALCULATE(

SUMX(VALUES('Calendar'[Add on]),'Calendar'[Add on])

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

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

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

+ 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

Could you please help me

Thank you so much

I

don't understand what's wrong and why my measure stopped

Could you please help me

Thank you so much

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

08-04-2021
06:18 AM

@tahar1407 Super difficult to understand without sample data and expected output. Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:

1. Sample data as text, use the table tool in the editing bar

2. Expected output from sample data

3. Explanation in words of how to get from 1. to 2.

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

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

09-12-2023
06:55 AM

Hi @Greg_Deckler ,

I have created the quick measure to calculate the moving average with dynamic slicer. But facing column total error for the measure. Like it has to sum Jan to Dec data and show it in the column but column total only taking Dec month data everytime.

Example - Should be Oct+Nov+Dec = 75379+62685+26346 = 164410 But the total showing is 26346.

Please help me with the following issue

I am getting column total error and taking Matrix table

Rows - Country,ProductCategory

Column - Month

I'm getting column total incorrect for Forecast(Blue) data.

Dax Formula (Quick Measure) - Product_Count rolling average =

IF(

ISFILTERED('Table_name'[dimdate]),

ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])

VAR __DATE_PERIOD =

DATESBETWEEN(

'Table_name'[dimdate].[Date],

STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),

__LAST_DATE

)

RETURN

AVERAGEX(

CALCULATETABLE(

SUMMARIZE(

VALUES('Table_name'),

'Table_name'[dimdate].[Year],

'Table_name'[dimdate].[QuarterNo],

'Table_name'[dimdate].[Quarter],

'Table_name'[dimdate].[MonthNo],

'Table_name'[dimdate].[Month]

),

__DATE_PERIOD

),

CALCULATE(

SUM('Table_name'[Product_Count]),

ALL('Table_name'[dimdate].[Day])

)

)

)

Thanks!

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

06-14-2021
04:58 PM

What is the syntax for this approach in Excel2013, i.e. without declaring a variable?

Anonymous

Not applicable

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

04-08-2021
08:29 AM

Found this just in time, and saved my laptop from being smashed into pieces. Thank you good sir.

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

03-18-2021
10:01 PM

Hi @Greg_Deckler,

I am not getting 100% on the table when I calculate the utilization %.

I have created a column:

Utilization = DIVIDE(CALCULATE(SUM(Utilisation_SAC[Total Hours])-SUM(Utilisation_SAC[Hours])),SUM(Utilisation_SAC[Total Hours])).

please help me out.