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

## 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.

8 REPLIES 8
Highlighted
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.

Proud to be a Super User!

Highlighted
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

---------------------------------------

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

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

## Re: SUMX calculated Total different from Aggregation

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.

---------------------------------------

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

Proud to be a Super User!

Highlighted
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]

 Sector RSP Period n other filter columns XX PTL ODP (Calculated Measure) A 1 filter values 0.020202 A 1 filter values 0.013423 A 1 filter values 0.006644 A 2 filter values 0.000135 A 2 filter values 0.006914 A 3 filter values 0.013693 A 3 filter values 0.020472 A 4 filter values 0.027251 B 1 filter values 0.034030 B 2 filter values 0.040809 B 3 filter values 0.047588 B 4 filter values 0.054367 B 1 filter values 0.061146 B 2 filter values 0.067925 A 4 filter values 0.074704 A 4 filter values 0.081483 A 4 filter values 0.088262 A 4 filter values 0.095041 A 4 filter values 0.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

## 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.

Highlighted
Community Support

## Re: SUMX calculated Total different from Aggregation

Hi @btwilkins ,

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.

Announcements

#### Power Platform Community Conference

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

#### Experience what’s next for Power BI

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

#### Community Summit North America – Join Online!

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

Top Solution Authors
Top Kudoed Authors