cancel
Showing results for
Search instead for
Did you mean:
slashfemme 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.]  1 ACCEPTED SOLUTION

Accepted Solutions 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

## 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

## 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. 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! Highlighted 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

## 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.  Super User

You are welcome.