10-21-2018 06:09 AM - last edited 04-01-2021 13:16 PM
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
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
)))
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?
@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.
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:
@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.
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"
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?
Hi,
Was just wondering if you have found a solution to this.? I too have a forecasting measure and cannot get the correct sum.
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
@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.
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!
What is the syntax for this approach in Excel2013, i.e. without declaring a variable?
Found this just in time, and saved my laptop from being smashed into pieces. Thank you good sir.
Hi @Greg_Deckler,
I am not getting 100% on the table when I calculate the utilization %.
I have created a column:
please help me out.
@shivakoriginja - So that looks like your m_Single measure. You would need an m_Total measure like the following:
m_Total = VAR __table = SUMMARIZE('Table',[Venue],"__value",[Utilization]) RETURN IF(HASONEVALUE(Table[Venue]),[Utilization],SUMX(__table,[__value]))
hi @Greg_Deckler,
|there is no change in the result. still I am not getting 100% there are no filters applied.
I have fact table with 8 dimensions and average grand total should work based on selected dimension. Please guide me How to correct the grand total based on selections
User validating this data from AAS using excel plugin. We can’t tell what dimension is going to selected for analysis
Measure 1 = SUM(Amount)
Measure 2 = Total days in 3 Months (Need to ignore the missing months based on dimension).
Avg = Measure 1 / Measure 2
Sample data
Days |
Period |
Client |
Channel |
Region |
Agent |
Product |
Amount |
|
31 |
202001 |
A1 |
C1 |
R1 |
A1 |
P1 |
100 |
|
29 |
202002 |
A1 |
C1 |
R1 |
A1 |
P1 |
200 |
|
31 |
202003 |
A1 |
C1 |
R1 |
A3 |
P2 |
300 |
|
31 |
202001 |
A2 |
C1 |
R1 |
A3 |
P4 |
150 |
|
29 |
202002 |
A3 |
C1 |
R1 |
A1 |
P1 |
250 |
|
31 |
202003 |
A2 |
C1 |
R1 |
A1 |
P1 |
350 |
|
31 |
202003 |
A2 |
C2 |
R2 |
A2 |
P1 |
450 |
|
|
|
|
|
|
|
|
|
|
Report Based on Client |
Amount |
Days |
Avg |
|
|
|
|
|
|
C1 |
1350 |
91 |
14.84 |
|
|
|
|
|
C2 |
450 |
31 |
14.52 |
|
|
|
|
|
|
1800 |
91 |
19.78 |
Dax Calculation (Days in 3 Months At grand total) |
|||
|
|
|
|
29.35 |
Expected Avg Total |
|
|
|
Report Based on Agent |
Amount |
Days |
Avg |
|
|
|
|
|
|
A1 |
900 |
91 |
9.89 |
|
|
|
|
|
A2 |
450 |
62 |
7.26 |
|
|
|
|
|
A3 |
450 |
31 |
14.52 |
|
|
|
|
|
|
1800 |
91 |
19.78 |
Dax Calculation (Days in 3 Months At grand total) |
|||
|
|
|
|
31.66 |
Expected Avg Total |
|
|
Tried some possible options to fix in AAS.
Return
SUMX(Table,(Table[Column] / Days) -- Tried calculate at each row level .
To correct the grand totals we need to use below syntax. But this syntax we can't achieve while using Pivot tables.
Hi All,
Any ideas on above issue. AAS should calculate average grand total based on selected slicer in excel pivot table. I am happy to write multiple if conditions if we have solution for this.
How to find what all slicers selected to apply if condition.
SUMX(
SUMMARIZE(<Fact Table>, [Fact Table][Column1], [Fact Table][Column2], [Fact Table][Column3], "Measure Name", <Measure>),
<Measure Name>)
Will this works for all dimensions. I hope with this we might hit with performance issue.
Please suggest some taughts to find solution for this
Hi,
I have a calculated field Goal which is not giving the sum correct.
How do I make it working?
Its 2020 - this had stumped me for 3 hours and yet here we are. Thank you sir, thank you so very much!