Some only write measures on data they can see in tables. This is often a bad habit.

Requirement

Calculate the median total sales of all Products.

…or maybe it’s better said this way:

Calculate aggregated sales for each Product. Display the median amount.

Final Visual

A simple single value:

Set-up

Just two tables for this post:

Final Measure

1step group median = 
MEDIANX(
      SUMMARIZE(
                fctSales,
                fctSales[Product Key],
                "Median Group Sales", CALCULATE(sum(fctSales[Revenue]))
      ),
      [Median Group Sales]
)

1 step measure (uses a virtual table). I compare physical vs virtual solutions below.

Pbix File

Solution Path Review

Now I’ll review two different ways to solve this.

Physical or Virtual?

Table fctSales has individual transactions. To answer the question we need aggregated sales for each Product. How should we aggregate?

• create a physical aggregated table?
• create a virtual aggregated table inside the measure?

Physical Table

DAX below creates a physical table:

aggSales =
SUMMARIZE(
    fctSales,
    fctSales[Product Key],
    "SalesByProduct", CALCULATE( SUM( fctSales[Revenue] ) )
)

We can see the aggregated amount for each product. What’s the problem?

In large models if we create a table for every intermediary step the file size increases and the data model becomes cluttered.

Final measure ‘addMedian’ references the aggregated column values:

addMedian = MEDIAN(aggSales[SalesByProduct])

Virtual Table

A single measure calculates everything. The final measure, repeated below, contains a virtual table using the SUMMARIZE function.

1step group median = 
MEDIANX(
      SUMMARIZE(
                fctSales,
                fctSales[Product Key],
                "Median Group Sales", CALCULATE(sum(fctSales[Revenue]))
      ),
      [Median Group Sales]
)

The disadvantage is that we can’t see the values inside the virtual table. Testing can be done using DAX Studio (see below).

Virtual tables unleash a world of possibilities. Complex questions can be answered inside a single measure but it takes time to learn.

Solution Pitfall

Using a variable to calculate the sum doesn’t work. Why?

I read somewhere that it calculates when defined and can’t be changed when used later (eg inside SUMMARIZE). Measure below doesn’t work.

1step group median NO =
VAR AllSales =
    CALCULATE( SUM( fctSales[Revenue] ) ) 
//doesn't work as it calcs a single value that can't be changed later on
RETURN
    MEDIANX(
        SUMMARIZE( fctSales, fctSales[Product Key], "Median Group Sales", AllSales ),
        [Median Group Sales]
    )

Measure Testing

DAX Studio

Use DAX Studio to see/test results for each part of a measure without creating excessive tables in the pbix file.

Excellent tutorial: Writing DAX Queries (daxstudio.org)

Connect to Pbix file

Query Syntax

The aggregation part of my measure after keyword EVALUATE. Press the Play button to see the results.

Results above are the same as table aggSales (physical table in pbix).

Summary

I occasionally create a table using DAX but it should be an exception not a rule.

It takes me a long time to understand some requirements. I’m getting better at translating them into the DAX language.

Learning DAX Studio does require practice but it’s worth it as there are so many benefits. And it’s free!