Reply
Highlighted
Visitor
Posts: 1
Registered: ‎04-18-2017

Aggregating after data is grouped/rolled-up

I have two tables joined one to many in power bi desktop.

 

So it does a good job automatically summing up numbers based on that field.

 

But I'm trying to roll-up an AVG which is based on two other columns. And it just keeps summing my averages.

 

Simplified model below. 

 

powerbi.jpg

 

There is one "Widget A" record in Table A. There are multiple "Widget A" records in Table B which is where Revenue and Orders roll up. So "Item" is from Table A and "Revenue" and "Orders" are from Table B.

 

I simply want the *rolled up* Revenue and the *rolled up* Orders to be able to be calculated on to make a new AVG.

 

I'm probably missing something obvious...

Super Contributor
Posts: 3,710
Registered: ‎07-11-2015

Re: Aggregating after data is grouped/rolled-up

Check out my Super Group design pattern, I think it may help: 

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

In Ohio? Check out the Columbus Azure ML and Power BI User Group (CAMLPUG)
In lieu of a kudo, consider donating to The Buckeye Ranch
Moderator
Posts: 2,210
Registered: ‎03-06-2016

Re: Aggregating after data is grouped/rolled-up

Hi @abertolett,

 

Based on your description, I have created sample data about Table A and Table B (See attached .pbix file), you can try the measure below:

 

Revenue/Orders = DIVIDE(CALCULATE(SUM('Table B'[Revenue]), FILTER(ALL('Table B'),'Table B'[Item] = MAX('Table A'[Item]))),CALCULATE(SUM('Table B'[Orders]), FILTER(ALL('Table B'),'Table B'[Item] = MAX('Table A'[Item]))))

 

Best Regards,
Qiuyun Yu

Attachment