- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculate then Aggregate

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

Johnny

Frequent Visitor

Calculate then Aggregate

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2016
12:25 AM

Hi,

I'm trying to find a way to keep a calculation on a detailed grain and once it's done on this level then aggregate it (SUM) the rest of the way to the top.

The formula is basically a X*Y (different grains though) and this works fine on the lowest level - so far so good.

Now I want to sum these results (eg. a total for all customers) and this results in 0 because the Y on this level is 0 - makes sense to me. Hence I want to CALCULATE (on product and customer level) first then AGGREGATE the calculated result in stead of "just" calculating it on Customer.

My table looks something like this:

Calculation (X and Y Values are Measures) | |||||

Customer | Product | Y Value | X Value | Current Result | Desired Result |

1 | 1 | 10 | 1 | 10 | 10 |

2 | 1 | 20 | 2 | 40 | 40 |

3 | 1 | -30 | 3 | -90 | -90 |

1 | 2 | 5 | 4 | 20 | 20 |

2 | 2 | 10 | 5 | 50 | 50 |

3 | 2 | -15 | 6 | -90 | -90 |

1 | 3 | -15 | 7 | -105 | -105 |

2 | 3 | -30 | 8 | -240 | -240 |

3 | 3 | 45 | 9 | 405 | 405 |

Aggregations | |||||

Customer | Product | Y Value | X Value | Current Result | Desired Result |

1 | 0 | 12 | 0 | -75 | |

2 | 0 | 15 | 0 | -150 | |

3 | 0 | 18 | 0 | 225 | |

1 | 0 | 6 | 0 | -40 | |

2 | 0 | 15 | 0 | -20 | |

3 | 0 | 24 | 0 | 60 |

I've tried to use this formula:

SUMX(

SUMMARIZE(

Customer;

Product);

X*Y)

This results in 0 in all levels (both higher and lower grain).

I can find plenty resources explaining how I aggregate then calculate - but not calculate then aggregate.

Hope anyone can help out - it will be greatly appreciated.

Kind regards,

J.

4 REPLIES 4

PavelR

Established Member

Re: Calculate then Aggregate

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2016
01:34 AM

Johnny

Frequent Visitor

Re: Calculate then Aggregate

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2016
01:55 AM

Hi Pavel,

Thanks for replying! )

My bad - if you take the information out in a pivot table I see this:

I have data like this:

Product | Customer | Y Value | X Value |

a | x | 10 | 2 |

a | y | 20 | 4 |

a | z | -30 | 6 |

b | x | 35 | 1 |

b | y | -80 | 3 |

b | z | 45 | 5 |

c | x | -45 | 9 |

c | y | 60 | 8 |

c | z | -15 | 7 |

And a measure which is X*Y

This gives me this result in a pivot table for illustration:

Sum of Result Pivot | ||

Product | Customer | Total |

a | x | 20 |

y | 80 | |

z | -180 | |

a Total | 0 | |

b | x | 35 |

y | -240 | |

z | 225 | |

b Total | 0 | |

c | x | -405 |

y | 480 | |

z | -105 | |

c Total | 0 | |

Grand Total | 0 |

Product a)

Clearly the 20+80-180 results in -80 and not 0 as above - and I want it to say -80.

One caveat is that I can't calculate the total in a column - as it is a somewhat complex measure and does not provide me with the correct result.

Hope it makes better sense??

Rgds,

J

PavelR

Established Member

Re: Calculate then Aggregate

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2016
02:04 AM

I used your data and I have this result:

I just created Total measure: Total = SUMX(Data;Data[X Value]*Data[Y Value])

and I used it as Value in pivot table.

Hope it is what you wanted.

Regards.

Pavel

v-yulgu-msft

Community Support Team

Re: Calculate then Aggregate

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2016
03:35 AM

Hi, @Johnny

Based on my test, if I added a matrix visualization to aggregate data, the total value displayed as -80 correctly. So, what is the entire measure for Total like in your scenario? Would you please share your pbix file for more analysis if possible?

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu

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

If this post