topic Re: Calculating weighted avg on rows with shared descriptions in DAX Commands and Tips

Calculating weighted avg on rows with shared descriptions

Nick-LWI — Mon, 13 Feb 2023 19:02:00 GMT

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!

Re: Calculating weighted avg on rows with shared descriptions

tamerj1 — Mon, 13 Feb 2023 19:23:29 GMT

Hi Nick-LWI

weighted average =
AVERAGEX (

'Table',

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

)

Re: Calculating weighted avg on rows with shared descriptions

Nick-LWI — Mon, 13 Feb 2023 19:40:07 GMT

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.

Re: Calculating weighted avg on rows with shared descriptions

BrianConnelly — Mon, 13 Feb 2023 20:56:14 GMT

Two ways you can write them....

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

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

Re: Calculating weighted avg on rows with shared descriptions

Nick-LWI — Mon, 13 Feb 2023 21:44:36 GMT

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.

Re: Calculating weighted avg on rows with shared descriptions

smpa01 — Mon, 13 Feb 2023 22:44:13 GMT

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

Re: Calculating weighted avg on rows with shared descriptions

Nick-LWI — Mon, 13 Feb 2023 23:18:14 GMT

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?

Re: Calculating weighted avg on rows with shared descriptions

smpa01 — Mon, 13 Feb 2023 23:52:43 GMT

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

Re: Calculating weighted avg on rows with shared descriptions

Nick-LWI — Tue, 14 Feb 2023 00:02:04 GMT

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

Re: Calculating weighted avg on rows with shared descriptions

smpa01 — Tue, 14 Feb 2023 00:24:20 GMT

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

Re: Calculating weighted avg on rows with shared descriptions

BrianConnelly — Tue, 14 Feb 2023 14:02:24 GMT

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

Re: Calculating weighted avg on rows with shared descriptions

BrianConnelly — Tue, 14 Feb 2023 14:07:05 GMT

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.

Re: Calculating weighted avg on rows with shared descriptions

smpa01 — Tue, 14 Feb 2023 14:23:56 GMT

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

Re: Calculating weighted avg on rows with shared descriptions

Nick-LWI — Tue, 14 Feb 2023 16:35:08 GMT

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.