Forum Discussion
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.
// if this is a solution please mark as such
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
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.
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.
// if this is a solution please mark as such
Thanks! This is what I'm after. Your help is much appreciated.
Regards- rovercon