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

[snph1777]

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.

Any ideas please, using DAX?

[Syndicate_Admin]

Friends

I need to perform a subtraction between 2 columns inventory inputs minus inventory outputs, I would think it is the function A-B = C, but I have another variable which is the position in the warehouse of the product a product can be in several positions and have several outputs.

How do I get ending inventory per item after subtracting outputs?

[best_lina]

Hello, I have the same need to subtract two columns in PBI matrix. I wish to know more about your solution. Can you please help? Please let me know. I am thinking about getting in contact so I can see in video (preferably) how you did it. I can accept any other methods of communication. Thank you! 

[v-xulin-mstf]

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

[snph1777 · 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.

[Syndicate_Admin]

Hello bunos dias atodos

I request your amble help to solve a doubt, I have this Matrix and I have not found how to get the differential for the 2 variables A A2 that those variables you change through a filter (TEST) that appears in the image, this result can be in another matrix or table there is no problem

in advance thanks

[amitchandak]

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

[snph1777]

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.