Forum Discussion

rovercon's avatar
rovercon
New Member
8 years ago
Solved

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 tro...
Need Help
  • samdthompson's avatar
    samdthompson
    8 years ago

    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

samdthompson's avatar
samdthompson
Memorable Member
8 years ago

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

rovercon's avatar
rovercon
New Member
8 years ago

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

 

Regards-  rovercon