cancel
Showing results for
Did you mean:  Resolver I

## Subtract two corresponding columns in a Power BI matrix: DAX help needed

I have a single measure in the Power BI Desktop matrix below. 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. 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.

1 ACCEPTED SOLUTION  Resolver I

I figured out:

Variance =

VAR Cat1 = MAXX (ALLSELECTED (Table1[Category]), Table1[Category])
VAR Cat2 = MINX (ALLSELECTED (Table1[Category]), 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 )

Cat1 and Cat2 variables come from the Slicer.

4 REPLIES 4  Community Support

Hi, @snph1777

Given my understanding of you need, do you want to calculate variance base on slicer?

Would you provide sample data and expected output?

Sample data and expected output would help tremendously.

Best Regards,  Resolver I

I figured out:

Variance =

VAR Cat1 = MAXX (ALLSELECTED (Table1[Category]), Table1[Category])
VAR Cat2 = MINX (ALLSELECTED (Table1[Category]), 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 )

Cat1 and Cat2 variables come from the Slicer.  Super User IV

@snph1777 , You can have measure like

calculate(SUM(Table[Column]), filter(Table, Table[CATEGORY] ="A")) - calculate(SUM(Table[Column]), filter(Table, Table[CATEGORY] ="B"))

Or measure like this one, where max(year) can be replace with actual year or max(Year)-1 etc

calculate(SUM(Table[Column]), filter(allselected(Table), Table[CATEGORY] ="A" && [year] =max([Year]) && [Qtr] =max([QTR])))
- calculate(SUM(Table[Column]), filter(allselected(Table), Table[CATEGORY] ="B" && [year] =max([Year]) && [Qtr] =max([QTR])))

Proud to be a Super User!  Resolver I

There is one more piece of information I need to provide; 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 2019 and 2018,
while Category B may have 2019 and 2017,
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. Announcements #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  