Weighted average in DAX with information on different rows

[luigi_mittel]

I am an Excel user who, for a few months now, has started using Power Pivot to optimize and simplify data analysis, therefore I have also started using DAX.

Below I present a problem I am encountering regarding the calculation of a weighted average, which, however, needs to use information that is on different rows. 

NB The table and the data I will present to you were invented by me to recreate the situation I faced in a simpler way. So, do not focus on the type of data but on their structure.

In following table, each customer orders a certain quantity of product (ITA biscotti = ENG biscuit, ITA gallette = ENG rice cake). However, the company is facing two scenarios (see the "scenario" column): one where prices rise ("inflation") and one where prices do not rise ("not inflation").

What I want to calculate is the average inflation. Obviously, in this case, each customer has a specific inflation, but as mentioned earlier, it's an invented scenario. 

The problem I encountered is that, having the price with inflation and without inflation on different rows, I struggle to properly aggregate at higher levels than the individual customer.

Since each customer orders different quantities of products, the average inflation must be a weighted average based on the quantities purchased.

Below, I will provide the measure I created, which is correct only for individual customers (see table below):

VAR TabInflation = FILTER(Tabella1, Tabella1[scenario] = "inflation")

VAR InflationPrice = MAXX(TabInflation;Tabella1[price])

VAR TabNotInflation = FILTER(Tabella1, Tabella1[scenario] = "not inflation")

VAR InflationPrice = MAXX(TabNotInflation;Tabella1[price])

VAR InflationRate = DIVIDE(InflationPrice-NotInflationPrice;NotInflationPrice)

VAR TotalQuantity = SUM(Tabella1[quantity])

RETURN 

SUMX(Tabella1; Inflationrate* Tabella1[quantity]) / TotalQuantity

Thank you for your attention 🙂

[bhanu_gautam]

@luigi_mittel , Try using

dax
WeightedAverageInflationRate =
VAR TabInflation = FILTER(Tabella1, Tabella1[scenario] = "inflation")
VAR TabNotInflation = FILTER(Tabella1, Tabella1[scenario] = "not inflation")

VAR InflationPrices = SUMMARIZE(TabInflation, Tabella1[customer], Tabella1[product], "InflationPrice", MAX(Tabella1[price]))
VAR NotInflationPrices = SUMMARIZE(TabNotInflation, Tabella1[customer], Tabella1[product], "NotInflationPrice", MAX(Tabella1[price]))

VAR InflationRates = ADDCOLUMNS(
InflationPrices,
"NotInflationPrice", LOOKUPVALUE(NotInflationPrices[NotInflationPrice], NotInflationPrices[customer], [customer], NotInflationPrices[product], [product]),
"Quantity", LOOKUPVALUE(Tabella1[quantity], Tabella1[customer], [customer], Tabella1[product], [product], Tabella1[scenario], "not inflation"),
"InflationRate", DIVIDE([InflationPrice] - [NotInflationPrice], [NotInflationPrice])
)

VAR TotalQuantity = SUMX(InflationRates, [Quantity])
VAR TotalWeightedInflation = SUMX(InflationRates, [InflationRate] * [Quantity])

RETURN
DIVIDE(TotalWeightedInflation, TotalQuantity)

[luigi_mittel]

Thanks a lot, but I get the following error:
Table variable cannot be used in the current context because a base table is expected.
Excel gives me the error for the variable InflationRatio, specifically in the LOOKUPVALUE step.

[Anonymous]

Hi @luigi_mittel ,

The formula which bhanu_gautam provided can return the same result with yours, could you please mark his last reply as the solution? It would be helpful to others when they face the similar problem as yours. Thank you.

Best Regards

[bhanu_gautam · Accepted solution]

Use Natural join insted of Lookup

DAX
WeightedAverageInflationRate =
VAR TabInflation = FILTER(Tabella1, Tabella1[scenario] = "inflation")
VAR TabNotInflation = FILTER(Tabella1, Tabella1[scenario] = "not inflation")

VAR InflationPrices = SUMMARIZE(TabInflation, Tabella1[customer], Tabella1[product], "InflationPrice", MAX(Tabella1[price]))
VAR NotInflationPrices = SUMMARIZE(TabNotInflation, Tabella1[customer], Tabella1[product], "NotInflationPrice", MAX(Tabella1[price]))

VAR InflationRates = ADDCOLUMNS(
NATURALINNERJOIN(InflationPrices, NotInflationPrices),
"Quantity", LOOKUPVALUE(Tabella1[quantity], Tabella1[customer], [customer], Tabella1[product], [product], Tabella1[scenario], "not inflation"),
"InflationRate", DIVIDE([InflationPrice] - [NotInflationPrice], [NotInflationPrice])
)

VAR TotalQuantity = SUMX(InflationRates, [Quantity])
VAR TotalWeightedInflation = SUMX(InflationRates, [InflationRate] * [Quantity])

RETURN
DIVIDE(TotalWeightedInflation, TotalQuantity)