cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Struggling with the Measure Totals

Hi All,

I've read through many of the articles/posts already made on Measure totals not adding up correctly. I've tried to apply but I cannot get it to work and hoping someone might be able to help me.

Essentially I have the following data:

 Client Person Year Revenue Year 1 Pct Year 2 Pct A A 2017 100 0.6 0.4 A A 2018 150 0.6 0.4 A B 2017 100 0.4 0.6 A B 2018 150 0.4 0.6

And I'm trying to get to this result:

 Client Person Revenue Year 1 Revenue Year 2 Carry Revenue New Revenue Carry Revenue Alloc New Revenue Alloc Year 1 Allocation Year 2 Allocation A A 100 150 100 50 40 30 60 70 A B 100 150 100 50 60 20 40 80 TOTAL 200 300 200 100 100 50 100 150

I've deployed the following measures:

Revenue Year 1 = calculate(sum([Revenue]),[Year]=2017)

Revenue Year 2 = calculate(sum([Revenue]),[Year]=2018)

Carry Revenue = if([Revenue Year 1]>[Revenue Year 2],[Revenue Year 2],[Revenue Year 1])

New Revenue = if([Revenue Year 2]>[Revenue Year 1],[Revenue Year 2]-[Revenue Year 1],0)

Carry Revenue Alloc: [Carry Revenue]*max(Year 2 Pct)

New Revenue Alloc: [New Revenue]*max(Year 1 Pct])

Year 1 Allocation: [Revenue Year 1] * max(Year 1 Pct])

Year 2 Allocation: [Carry Revenue Alloc] + [New Revenue Alloc]

I'm able to get the line by line to be accurate as above, but the totals are not adding up properly. I'm probably getting tripped up where I am using MAX to apply the percentage to multiply against, but after a couple of different attempts I just haven't been able to figure this out.

Appreciate any help, thank you!

1 ACCEPTED SOLUTION
Frequent Visitor

To close the loop, I ended up creating a new table and summarizing the measures. It's an extra step, but it works. Thanks all

6 REPLIES 6
Super User

So, you can read this but you may have already found it: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

But, one of the most fool proof ways that I have gotten this to work is to keep all of your individual measures and for each fo them write a corresponding measure like this:

Revenue Year 1 Total =
VAR __table = SUMMARIZE('Table',[Client],[Person],"__revenueyear1",[Revenue Year 1])
RETURN
IF(HASONEVALUE('Table'[Client]),[Revenue Year 1],SUMX(__table,[__revenueyear1]))

Something along those lines. So, basically you are summarizing your table exactly has it appeas in the matrix/table you have and including in the summarization the individual measure that you wrote. Then, if you are on an individual row, you simply use that measure. Alternatively, you do a SUMX (or other aggregation) across your summarized table/matrix.

You might find this one interesting as well, Matrix Measure Total Triple Threat Rock & Roll:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Thanks, I did read the first post but trying to apply its principals are proving elusive to me.

Without changing anything, I finally realize that the existing formulas, as you stated in your other post, are acting exactly as intended. The calculated total on the measure is applying the Max percentage to the total revenue column, e.g. 673046.19 * 0.9.

I think one way to solve for this is to pivot the rows to columns in the original query, but I was hoping DAX could help me avoid this since it takes awhile to load.

 ALLOC_YR1_PCT ALLOC_YR2_PCT ALLOC_YR1_AMT ALLOC_YR2_AMT CARRY_YR2_EXT_ALLOC CARRY_YR2_NEW_ALLOC TOTAL_REV_YR1 TOTAL_REV_YR2 0.90 0.60 11689.36 49714.81 7792.90 41921.91 12988.17 59568.07 0.10 0.40 1298.82 9853.26 5195.27 4657.99 12988.17 59568.07 0.00 0.00 0.00 0.00 0.00 0.00 12988.17 59568.07 0.00 0.00 0.00 0.00 0.00 0.00 12988.17 59568.07 0.00 0.00 0.00 0.00 0.00 0.00 12988.17 59568.07 0.90 0.60 109458.96 16554.48 16554.48 0.00 121621.06 27590.81 0.00 0.00 0.00 0.00 0.00 0.00 121621.06 27590.81 0.00 0.00 0.00 0.00 0.00 0.00 121621.06 27590.81 0.00 0.00 0.00 0.00 0.00 0.00 121621.06 27590.81 0.10 0.40 12162.11 11036.32 11036.32 0.00 121621.06 27590.81 Correct TOTALS 134609.24 87158.87 40578.98 46579.89 673046.19 435794.37 0.90 MAX 0.60 MAX What BI shows as the total 605741.6 261476.6
Community Support

Hi @ijmiller16,

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Frequent Visitor

Thanks, this gets me closer but it changes the values just slightly. I'm not sure why.

For example sum value is 210,460.33

SUMX makes it 212,321.99

Frequent Visitor

To close the loop, I ended up creating a new table and summarizing the measures. It's an extra step, but it works. Thanks all

Community Support

Hi @ijmiller16,

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors