Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
btwilkins
Frequent Visitor

SUMX calculated Total different from Aggregation

I'm sure this is a really simple issue that I am completely missing but when I am calculating a column total based on two variables;

Sector "WCB"

RSP Period "20/09"

 

XXPTL TOTAL =
CALCULATE (
    SUMX ( 'Fct SG PTL ODP Period', 'Fct SG PTL ODP Period'[xx PTL ODP] )
)
 
 
I Get
 
Rsp Period Sector XXPTL TOTAL XX PTL ODP(Aggregation)
20/09 WCB 3.56 3.25
 
WTF!!??
 
Help please - Do I have some error in the cube?
1 ACCEPTED SOLUTION

Hi @btwilkins ,

 

Since I don’t know the formula of the measure  'XX PTL ODP ', I can’t find the cause of the error, you can try to do so.

XXPTL TOTAL = 
IF(
    HASONEVALUE('Fct SG PTL ODP Period'[Sector]),
    'Fct SG PTL ODP Period'[xx PTL ODP],
    SUMX( ALL('Fct SG PTL ODP Period'), 'Fct SG PTL ODP Period'[xx PTL ODP])
)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

My issue isn't being resolved because XX PTL ODP is a calculated Measure IF's and SUMs don't work?

Hi @btwilkins ,

 

"My issue isn't being resolved because XX PTL ODP is a calculated Measure IF's and SUMs don't work?"

It is indeed for this reason, please give a sample data and give the formula og the measure 'XX PTL ODP'.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@btwilkins  - Let's back up. 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Apologies,

 

See my Fct table structure below;

[Table 1]

SectorRSP Periodn other filter columnsXX PTL ODP (Calculated Measure)
A1filter values0.020202
A1filter values0.013423
A1filter values0.006644
A2filter values0.000135
A2filter values0.006914
A3filter values0.013693
A3filter values0.020472
A4filter values0.027251
B1filter values0.034030
B2filter values0.040809
B3filter values0.047588
B4filter values0.054367
B1filter values0.061146
B2filter values0.067925
A4filter values0.074704
A4filter values0.081483
A4filter values0.088262
A4filter values0.095041
A4filter values0.101820

 

The calculation I want to do as a calculated measure (NOT column) is;

- £xxxx (fixed currency value) is shared out by Sector AND Period as a proportion of the SUM of all the XX PTL ODP in that Sector for that period, so I need to determine what is the proportion of XX PTL ODP for each row, what sector & period they are in then multiply the proportion of the fixed currency value by that proportion (for EVERY row).

 Therefore;

CALCULATED MEASURE = 

£xxx fixed currency value (for that sector & period) x ((XX PTL ODP)/ SUM (XX PTL ODP(for that sector&period)))

For each row in the dataset

 

I'm expecting a £ figure, I have set £xxx fixed currency values for each Sector Period

 

There are only two sectors but periods will be continuously increasing so I will update this code with every upload of new data as the £xxx are only known subsequently.

Hi @btwilkins ,

 

Has your problem been solved?

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @btwilkins ,

 

Since I don’t know the formula of the measure  'XX PTL ODP ', I can’t find the cause of the error, you can try to do so.

XXPTL TOTAL = 
IF(
    HASONEVALUE('Fct SG PTL ODP Period'[Sector]),
    'Fct SG PTL ODP Period'[xx PTL ODP],
    SUMX( ALL('Fct SG PTL ODP Period'), 'Fct SG PTL ODP Period'[xx PTL ODP])
)

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.