Measure Totals, The Final Word 04-08-2021 08:29 AM

[Greg_Deckler]

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

[hamedelbhrawy]

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

)

[scondon1985]

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?  

[stitch_es]

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

[terryh1]

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

[Greg_Deckler]

@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

[stitch_es]

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.

[Greg_Deckler]

@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.

[AmitWaware]

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

                )))

[canddig]

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 

[Power_Chum]

@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]

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?

[Greg_Deckler]

@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.

[Jdpatino21]

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?

[mdarlington]

Hi,

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

[tahar1407]

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

[Greg_Deckler]

@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.

[anushaghi123]

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!

[fcec703]

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

[Anonymous]

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

[shivakoriginja]

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.