Search code examples
matrixpowerbidaxdynamic-columns

Power BI matrix - Subtract two (dynamic) corresponding columns using DAX

I have a single measure in the Power BI Desktop matrix below. enter image description here

The measure comes from a single table, and is a sum of a column with numerical values.

The measure is: SUM(Table[Column])

Column Group 1: Category

Column Group 2: Year

Column Group 3: Qtr (i.e. Q)

I have a scenario, in which our client wants to get the variance (difference) between 2020 Q1 of Category A and 2020 Q1 of Category B.

enter image description here

The new column is Q1 Variance.

In a similar manner I need the variance between 2020 Q2 of Category A and 2020 Q2 of Category B.

How do I get a new column in a matrix based on this scenario, where I subtract corresponding columns in a Year and Quarter?

Also there may be situation later in which Category A can have a year that Category B may not have.

For example, Category A may have 2018 and 2019, while Category B may have 2017 and 2018.

In this scenario, I will need the difference between the 2018 Quarters only, don't need any computation on 2017 and 2019.

Any ideas please, using DAX?

The Category selection happens via a Slicer.

Sometimes it may be Category A and Category B, other times it may be Category B and Category C, that are chosen from the Slicer.

The slicer will always choose exactly 2 categories at a time.

So, I cannot hard code anything. Everything is dynamic.

Category A may have 2018 and 2019, while Category B may have 2017 and 2018, while Category C may have 2019, 2020, 2021, etc.

Also, I might have to compare the corresponding months too. Say Jan 2019 of A will be subtracted from Jan 2019 of B (similar to the Quarter comparison).

I think we may need to use some variable to match the year, and then move to quarter, and later to month.

Solution

  • If you have exactly two distinct categories, you can pick one using max and one using min and calculate the variance between the two.

    Variance =
VAR Cat1 = MAXX ( ALLSELECTED ( Table1 ), Table1[Category] )
VAR Cat2 = MINX ( ALLSELECTED ( Table1 ), Table1[Category] )
VAR Sum1 = CALCULATE ( SUM ( Table1[Column] ), Table1[Category] = Cat1 )
VAR Sum2 = CALCULATE ( SUM ( Table1[Column] ), Table1[Category] = Cat2 )
RETURN
    IF ( ISBLANK ( Sum1 ) || ISBLANK ( Sum2 ), BLANK (), Sum1 - Sum2 )

    This preserves the year and quarter/month context and only changes the category context to do the calculation.