cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: SUMX calculated Total different from Aggregation

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
Highlighted
Super User IX
Super User IX

Re: SUMX calculated Total different from Aggregation

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

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IX
Super User IX

Re: SUMX calculated Total different from Aggregation

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: SUMX calculated Total different from Aggregation

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

Highlighted
Super User IX
Super User IX

Re: SUMX calculated Total different from Aggregation

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: SUMX calculated Total different from Aggregation

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.

Highlighted
Frequent Visitor

Re: SUMX calculated Total different from Aggregation

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.

Highlighted
Community Support
Community Support

Re: SUMX calculated Total different from Aggregation

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

Highlighted
Community Support
Community Support

Re: SUMX calculated Total different from Aggregation

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors