Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
@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!
@Anonymous Don't feel bad man, this one comes up a LOT!! It is super unintuitive to people just learning DAX but is a great example to help expand your understanding of how important context is to DAX!
Below is a screen shot from my excel file that I have uploaded into Power BI.
I want to show that in Cook County (for my current period and for the fuel type water) that Brand 1 has sold a total of 8, which is equal to 13% share
In Power BI if I filter on Cook I get what I need.
When I add another County, the numbers get messed up.
Where am I going wrong. I’ve attached the pbix file. I am not a Dax guy, I am trying to learn. Thanks.
Hi,
It really solved my problem for one measure , but in another case am using the same approach that is giving the incorrect subtotal what can i do in that case i am not able to understand, I am using below measure after referring your another trick for subtotal-
hi - I used your solution at the lowest level of my hiearchy but now how do I get the numbers for aggregate up to the higher levels properly (see table below)?
thanks.
I suggest you post your question as a new thread in the "Help" Forum. I don't think this thread should be used to ask questions for Help. You will get many more eyes looking at it which will assist you further.
Also please include as much detail as you can.
Good Luck and Best Regards,
Just wanted to say a big Thank You for this post.
It helped me to get my Matrix Visual working the way it should!
Kindest Regards,
Firstly thanks Greg for this great article.
One Question
when dealing with a measure that is in itself a sumx , is there something i need to do differently in creating the variable table?
the formula works for each month but total is returnign zero and i ssupect it is due to the sumx formula not creating the summarizes table correctly.
After looking at this a bit have developed an alterative solution:
i created an index column in the data table for the level of calculations i need ie Customer + Product+ Month
Then i created a new table summarising that column and linked the new table back to the data table.
Then in the sumx formula used the new table as the reference Sumx( new table (custProdMonthnum), calculations)
this means it forces the total column to do the calcs at the month level, so all adds up. also it means just one formula and doesnt worry if you add in qrtly totals etc..
Thank you Greg. I was able to obtain the correct totals for a simple (yet critical) sum of absolute values calculation in Power Pivot by adapting your technique. What I thought would be a three second "wrap in ABS" formula turned into a three (12-hour) day nightmare. Cannot believe what I just went through to achieve what is literally the dead-simplest calculation mathematically possible. But can't thank you enough for this solution.
Do the formula work between two tables?
Thanks for your help
Hi @Greg_Deckler ,
I have a question regarding the use of a VAR in the summarization. In that case the result is wrong. I have to use a VAR due to a complex commission calculation based on different levels of revenue and % margin.
For instance, if revenue > $250,000 and margin % is less than 56% but greater than 52% the commision % is 1%,
with the same revenue limit if margin is less than 60% but greater than 56% the commission % is 1.5%, etc.
To handle this calculation I created VAR as follows:
Comision =
VAR Sales= [Sales Total]
VAR Margin = [Margin Total]
VAR Commission =
SWITCH( TRUE(),
Sales >= 200000 && Sales < 250000, 0,
Sales>= 250000 && Margin >= 0.52 && Margin < 0.56, 0.01,
Sales>= 250000 && Margin < 0.60, 0.015,
Sales>= 250000 && Margin >= 0.6, 0.02,
0
)
VAR BaseBonus =
SWITCH( TRUE(),
Sales>= 200000 && Sales < 250000, 1000,
0
)
VAR CommissionAmount = Sales * Commission + BaseBonus
VAR ComisTotal = SUMMARIZE( VALUES( OCRD[SlpCode] ), OCRD[SlpCode], "ComisTot", MAX( 0, CommissionAmount ) )
RETURN
SUMX( ComisTotal, [ComisTot] )
At using a VAR after RETURN it gives a wrong total.
I made a dummy data model to show this procedure here.
Could you please advice?
Thanks,
Fernando
Hello everyone,
I am trying to calculate some average times and it works well, except the Totals. Each row represents a candidate.
The table I have looks like this:
The formula I use for the column in the middle is:
Amazing! I just tried this Greg's solution after days of struggling with meassure totals for the first time and it worked perfectly. It is indeed amaizing that Microsoft hasn't come out with a solution of their won for this problem alredy coded in PBI, but this solution is a real wonder. Thanks so much Greg, you're a genious ! BTW, the poem is fantastic too! : )
How can one apply this solution to a measure that references two additional measures, as shown below?