cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rovercon Frequent Visitor
Frequent Visitor

Weighted average comparison

Have a table of item sales by division.    Each division is selling the same items at somewhat different pricing.   Would like to show how much division sales are above/below the Total Avg.    No trouble getting this into Power BI, or getting the Total Avg column.    I can summarize all the data I'm after in visualizations, but am having trouble creating the Division product average that can then be used in further calculations....like the Difference field.    New to Power BI, Many Thanks.

 

Please see below for what I'm using to the the 'Total Avg' measure.    The table at bottom is an idea of what I'm after.

 

Total Avg = SUM(Sheet1[Sales]) / SUM(Sheet1[Qty]).    

 

BI table.PNGBI test.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
samdthompson Established Member
Established Member

Re: Weighted average comparison

Oh so nothing calculated yet, gotcha. You will need a couple of measures:

 

Total Qty = SUM(Table1[Qty])

Average price = SUMX(Table1,Table1[Qty]*Table1[Price])/[Total Qty]

Average for company = CALCULATE([Average price],ALL(Table1[Division]),VALUES(Table1[Product]))

Difference = IF(HASONEVALUE(Table1[Division]),[Average for company]-[Average price],blank())

 

the HASONEVALUE bit is not completely necessary but its the bit behind the blanks at the total line on the matrix i have shown.

 

 

2018-05-24_11-04-48.png

 

 

 

 

 

 

// if this is a solution please mark as such

4 REPLIES 4
samdthompson Established Member
Established Member

Re: Weighted average comparison

Something along the lines of:

 

COMPANY AVERAGE = CALCULATE( [DIVISION AVERAGE] , ALL(SHEET1[DIVISION NAME]) , VALUES(SHEET1[PRODUCT]) )

 

 

 

 

// If this is a solution please mark as such

rovercon Frequent Visitor
Frequent Visitor

Re: Weighted average comparison

Thanks!   I'm working with the table at top and trying to replicate something similar to what is at bottom.   I'm working to create the Division Avg field, this isn't currently part of table in BI.    

samdthompson Established Member
Established Member

Re: Weighted average comparison

Oh so nothing calculated yet, gotcha. You will need a couple of measures:

 

Total Qty = SUM(Table1[Qty])

Average price = SUMX(Table1,Table1[Qty]*Table1[Price])/[Total Qty]

Average for company = CALCULATE([Average price],ALL(Table1[Division]),VALUES(Table1[Product]))

Difference = IF(HASONEVALUE(Table1[Division]),[Average for company]-[Average price],blank())

 

the HASONEVALUE bit is not completely necessary but its the bit behind the blanks at the total line on the matrix i have shown.

 

 

2018-05-24_11-04-48.png

 

 

 

 

 

 

// if this is a solution please mark as such

rovercon Frequent Visitor
Frequent Visitor

Re: Weighted average comparison

Thanks!   This is what I'm after.   Your help is much appreciated.   

 

Regards-  rovercon