DAX - summarize by averaged column

Question

I'm new to DAX, and struggling how to summarize by calculated column using DAX.
In my dataset below, how can I calculate the average sales by price point using DAX?

Limitations is that I cannot add price column (value / unit) on my DB. Thus, I need to calculate both price and sales by price in one DAX query.

Week	Value	Unit
W1	3000	3
W2	1500	1
W3	500	1
W4	1500	1
W5	2000	2

The result should be like this:

Price	Avg Value Sales
500	500
1000	2500
1500	1500

sergiom sergiom · Accepted Answer · 2021-01-14T15:38:21

Since you don't have a Price column, you cannot create a matrix visual with the price on the rows, since measures cannot be used for the columns. But you can implement a calculated table like this

Result = 
VAR Tab =
    ADDCOLUMNS ( Prices, "Price", Prices[Value] / Prices[Unit] )
VAR Res =
    ADDCOLUMNS (
        Tab,
        "Avg",
            VAR CurPrice = [Price]
            RETURN
                AVERAGEX ( FILTER ( Tab, [Price] = CurPrice ), Prices[Value] )
    )
RETURN
    DISTINCT ( SELECTCOLUMNS ( Res, "Price", [Price], "Avg", [Avg] ) )

First we declare a table variable from the source table (I called it "Prices") adding the Price column, then we iterate over this table variable adding the "Avg" column using AVERAGEX on the same table variable filtered by "Price". Finally we return the DISTINCT of the two columns Price and Avg.

Edit:
It's possible to reduce the number of rows adding a variable to get the distinct prices

Result1 = 
VAR Tab =
    ADDCOLUMNS ( Prices, "Price", Prices[Value] / Prices[Unit] )
VAR DistinctPrices = DISTINCT( SELECTCOLUMNS( Tab, "Price", [Price] ) )
VAR Res =
    ADDCOLUMNS (
        DistinctPrices,
        "Avg",
            VAR CurPrice = [Price]
            RETURN
                AVERAGEX ( FILTER ( Tab, [Price] = CurPrice ), Prices[Value] )
    )
RETURN
    DISTINCT ( SELECTCOLUMNS ( Res, "Price", [Price], "Avg", [Avg] ) )

DAX - summarize by averaged column

1 Answers