Help! :( Dynamic Weighted Average based on Revenue

[slashfemme]

Hello all!

I've been so stumped by this all day and it is driving me nuts, given how easy it is for me in Excel. Basically what I am trying to do is weight the average selling price of my items based on how much total revenue they brought in. If I have sold 100 items that each cost $1, and then have sold 10 items that cost $100 I don't want the average selling price for that group to be $10 (Average Selling Price = Revenue / Qty ), I want it to equal $91.00 to give weight to the product that is bringing in more revenue.

My Excel formulas: 
Price = Revenue / Qty

% of Total = Revenue/Total Revenue (& transformed to %)

Weighted Amt = % of Total * Price

In Power BI, I have a measure for both the Price and % of Total and can get to the Weighted Amount column, but it always shows the average and not the sum of the column. Is there a way to calculate this total weighted average amount as a measure so I can use it dynamically, switching between product categories and display as a Card?

Power BI Measures:

Price = DIVIDE([Total Sales],[Total Qty])

Wtd Price by Item = [Price] * [% of Total]

% of Total = DIVIDE([Total Sales], CALCULATE([Total Sales],ALLSELECTED()))

Thank you all for looking this over! [Not seen in Power BI table is a column to the left with item names.]

 

[Ashish_Mathur · Accepted solution]

Hi,

For the Weighted Amt, the measure should be:

=IF(HASONEVALUE(Data[Prod]),[Price] * [% of Total],SUMX(SUMMARIZE(VALUES(Data[Prod]),Data[Prod],"ABCD",[Price] * [% of Total]),[ABCD]))

Hope this helps.

[slashfemme]

I've never been so excited to get to work on a Monday to test a formula. 🙂 Thank you, @Ashish_Mathur! This formula appears to have worked correctly over different slicers. (Attached image showing measure at work named TEST WAV.)

The only thing I had a question about is the "ABCD" reference. I believe this is for the individual products, but in my case I have hundreds of products in different categories. I kept in the "ABCD" reference and it still worked, but curious as to what it is for.

Thank you again!

[Ashish_Mathur]

You are most welcome.  ABCD is the just the title of a new virtual column that i have inserted via the SUMMARIZE() function.  Down that column, i am simply multiplying Price and the %  .  Instead of ABCD, type any other relevant heading.

Hope this helps.

[slashfemme]

Thank you so much for clarifying! Now this function makes sense to me, and I can't wait to use it elsewhere now that I know how to use it. 🙂

[Ashish_Mathur]

You are welcome.