cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
snph1777
Resolver I
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.

 

PB1.GIF

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.

 

PB2.GIF

 

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?

 

1 ACCEPTED SOLUTION

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.

View solution in original post

4 REPLIES 4
v-xulin-mstf
Community Support
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.
Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Link Chen

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.

View solution in original post

amitchandak
Super User
Super User

@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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks Amit. Appreciate your help.

 

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!