Average Price by Territory, Manufacturer, Brand, Product, Date (DAX)

[Asantos2020]

Hello!

I got the following challenge which I haven't been able to overcome:

Territory      Fact   Manufacturer     Brand     Product    Date                  Value
North          Value  A                       X             Item1        12/31/2018      $10

South          Qty     A                       X             Item2        11/30/2018         5
North          Qty     B                       Y             Item3         01/31/2019        2

West           Value  C                       Z             Item4         02/28/2018      $11

In order to separate Value from Qty, I created 02 measures:

Value$ = CALCULATE(SUM(MarketShare[Value]);MarketShare[Fact] = "Value")
Volume = CALCULATE(SUM(MarketShare[Value]);MarketShare[Fact] = "Qty")

Simply using:

AvgPrice = (CALCULATE([Value$]/ [Volume]))

...gives me the average price, but when it's displayed on matrix table, with brand as rows and territory as columns.

If I want to use it on a bar chart and selected Territory in the Slicer, then I start getting infinity related errors.

Thanks in advance!

[v-diye-msft · Accepted solution]

Hi @Asantos2020 ,

I modified your measure as belows:

Value$ = CALCULATE(SUM(MarketShare[Value]),FILTER(ALL(MarketShare),[Fact] = "Value"),VALUES(MarketShare[Territory]))

Volume = CALCULATE(SUM(MarketShare[Value]),FILTER(ALL(MarketShare),[Fact]="Qty"),VALUES(MarketShare[Territory]))

AvgPrice = DIVIDE([Value$],[Volume])

Result shown as below: (AvgPrice will change based on territory)

[Anonymous]

What if you used the Divide function instead of "/" ?