Aggregating after data is grouped/rolled-up

[abertolett]

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. 

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

[v-qiuyu-msft]

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

[Greg_Deckler · Accepted solution]

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