Reply
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎04-18-2017
Accepted Solution

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


Accepted Solutions
Super Contributor
Posts: 4,404
Registered: ‎07-11-2015

Re: Aggregating after data is grouped/rolled-up


All Replies
Super Contributor
Posts: 4,404
Registered: ‎07-11-2015

Re: Aggregating after data is grouped/rolled-up

Moderator
Posts: 3,019
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