Calculating weighted avg on rows with shared descriptions

[Nick-LWI]

Hi, I'm fairly new to PowerBI so I'm sure there's an obvious solution that I'm unable to stumble into but here's what I'm trying to do: I need a formula that looks at the "Desc" field and calculates the weighted average of items that share a description.  There's a dummy table below.  The weighted average part is easy enough, but I don't know how to have it evaluate like items.  Also there's like 100 different item descriptions in my real data so I can't use a FILTER expression since it requires a hard reference and not a variable one.

Desc	Price	Qty
ABCD	10	3
ABCD	11	2
EFG	20	1
EFG	22	1
ABCD	9	2
EFG	18	1
YZ	100	5
YZ	110	1
EFG	15	4
ABCD	10	1
YZ	120	1

So I need a measure that says the weighted average price on ABCD is 10, EFG is 17.14, YZ is 104.28, etc.

Thanks for your help!

[smpa01]

@Nick-LWI  sure

Measure = 
DIVIDE (
    SUMX (
        FILTER ( ALL ( 'Table 1' ), 'Table 1'[Desc] = MAX ( 'Table 1'[Desc] ) ),
        'Table 1'[Price] * 'Table 1'[Qty]
    ),
    CALCULATE ( SUM ( 'Table 1'[Qty] ), ALLEXCEPT ( 'Table 1', 'Table 1'[Desc] ) )
)

[Nick-LWI]

Thank you smpa01, looks like this solution also gives me the correct weighted average per desc (I ended up using this:

CALCULATE(DIVIDE(SUMX(Table,Table[Price]*Table[Qty]),SUMX(Table,Table[Qty])),FILTER(ALLSELECTED(Table),Table[Desc]=MAX(Table[Desc])))

however I'm still unable to use the results that appear in a visual (ie matrix) for further DAX expressions.  If you reference the measure in another DAX formula it uses the same value for every single item, and not the individual item's respective value.  Using your picture as an example the Total of 33.18 is applied to everything, as opposed to 10 being applied to ABCD, 17.14 applied to DEF, etc.

I've exported the matrix to a csv and uploaded it as a table as a workaround, but obviously the data is no longer dynamic, so this is not ideal.  Is there any way to reference the results shown in the visual in DAX?

[smpa01]

@Nick-LWI  can you use the measure I gave you and see if it returns the same value in each cell?

[Nick-LWI]

Yes your measure does return the same value for all cells when applied to a new calculated column.

[smpa01 · Accepted solution]

@Nick-LWI  for calculated columns

Column = DIVIDE (
    SUMX (
        FILTER (  'Table 1' , 'Table 1'[Desc] = EARLIER ( 'Table 1'[Desc] ) ),
        'Table 1'[Price] * 'Table 1'[Qty]
    ),
    CALCULATE ( SUM ( 'Table 1'[Qty] ), ALLEXCEPT ( 'Table 1', 'Table 1'[Desc] ) )
)

[Nick-LWI]

Thank you! This worked.  I never would have thought about the EARLIER expression.  I appreciate you sticking around and helping me work throught it, I've marked as the solution.

[BrianConnelly]

You didn't mention that you were using a calculated column.  If that is the case, then your filter should be using the "Earlier" function, i.e.  Filter(Table, desc = Earlier(desc))

[smpa01]

Why are you using a measure for calculated column? They are two different things

[BrianConnelly]

Two ways you can write them....

Avg Total 2 = AVERAGEX(FILTER(ALLSELECTED('DataTable'),'DataTable'[Desc]=MAX('DataTable'[Desc])),'DataTable'[Price])

Or 

Avg Total = CALCULATE(AVERAGE('DataTable'[Price]),FILTER(ALLSELECTED('DataTable'),'DataTable'[Desc]=MAX('DataTable'[Desc])))

[Nick-LWI]

Thank you Brian, it looks like this is calculating correctly when the measure is brought into a table, but do you know how to incorporate the individual results of this measure into the DAX for other formulas?  I need to calculate Profit and when I use this measure it pulls in the total for all the DESC items and applies it to every DESC, when I want it to apply the respective value to each individual DESC.

[BrianConnelly]

Your sample data does'nt have enough context for me to understand how your gettting to Profit.  Can you provide the rough match so I know what you are attempting to do?  Also, if my last answer was your solution to your original question, please mark it as the correct solution. 

[tamerj1]

Hi @Nick-LWI 

weighted average =
AVERAGEX ( 

'Table',

'Table'[Price] * 'Table'[Qty] 

)

[Nick-LWI]

tamerj1, thank you for the reponse however I need a weighted average that varies based on the "Desc" field.  That variable weighted average is then used for profit calculations on individual orders.

Additionally your formula doesn't appear to work correctly with the data, for example on item "ABCD" it would yield 20 when the true weighted average is 10.  I believe that for a straight weighted average one would use DIVIDE(SUMX(table, table[Price]*table[Qty]),SUMX(table,table[Qty]) however this doesn't account for the differences in description.