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

Help! :( Dynamic Weighted Average based on Revenue

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

 

 wav.JPGpbi wav.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Help! :( Dynamic Weighted Average based on Revenue

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.

5 REPLIES 5
Super User
Super User

Re: Help! :( Dynamic Weighted Average based on Revenue

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 Frequent Visitor
Frequent Visitor

Re: Help! :( Dynamic Weighted Average based on Revenue

I've never been so excited to get to work on a Monday to test a formula. Smiley Happy 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!

worked.JPG

Super User
Super User

Re: Help! :( Dynamic Weighted Average based on Revenue

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 Frequent Visitor
Frequent Visitor

Re: Help! :( Dynamic Weighted Average based on Revenue

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

Highlighted
Super User
Super User

Re: Help! :( Dynamic Weighted Average based on Revenue

You are welcome.