Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Subtract two corresponding columns in a Power ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

12-29-2020
07:20 PM

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

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2021
12:14 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2021
12:12 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2021
12:14 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-29-2020
07:31 PM

@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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-30-2020
03:08 AM

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.

Announcements

Featured Topics

Top Solution Authors

User | Count |
---|---|

266 | |

82 | |

71 | |

62 | |

59 |

Top Kudoed Authors

User | Count |
---|---|

299 | |

90 | |

83 | |

82 | |

72 |