topic Re: Subtract two corresponding columns in a Power BI matrix: DAX help needed in Desktop
https://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1574842#M640566
<P>Thanks Amit. Appreciate your help.</P><P> </P><P>There is one more piece of information I need to provide; the Category selection happens via a Slicer.</P><P> </P><P>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.</P><P> </P><P>The slicer will always choose <U>exactly</U> 2 categories at a time.</P><P> </P><P>So, I cannot hard code anything. Everything is dynamic.</P><P> </P><P>Category A may have 2019 and 2018,<BR />while Category B may have 2019 and 2017,<BR />while Category C may have 2019, 2020, 2021, etc.</P><P> </P><P>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).</P><P> </P><P>I think we may need to use some variable to match the year, and then move to quarter, and later to month.</P>Wed, 30 Dec 2020 11:25:19 GMTsnph17772020-12-30T11:25:19ZSubtract two corresponding columns in a Power BI matrix: DAX help needed
https://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1574358#M640436
<P>I have a <U>single measure</U> in the Power BI Desktop <STRONG>matrix</STRONG> below.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PB1.GIF" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/424781i9D5B0DBE6F7BF270/image-size/large?v=v2&px=999" role="button" title="PB1.GIF" alt="PB1.GIF" /></span></P><P>The measure comes from a <U>single table</U>, and is a <U>sum</U> of a column with numerical values.</P><P> </P><P>The measure is: SUM(Table[Column])</P><P> </P><P>Column Group 1: Category</P><P>Column Group 2: Year</P><P>Column Group 3: Qtr (i.e. Q)</P><P> </P><P>I have a scenario, in which our client wants to get the variance (difference) between <U>2020 Q1 of Category A</U> and <U>2020 Q1 of Category B</U>.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PB2.GIF" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/424786i266B94963588F36B/image-size/large?v=v2&px=999" role="button" title="PB2.GIF" alt="PB2.GIF" /></span></P><P> </P><P>The new column is Q1 Variance.</P><P> </P><P>In a similar manner I need the variance between <U>2020 Q2 of Category A</U> and <U>2020 Q2 of Category B</U>.</P><P> </P><P>How do I get a new column in a matrix based on this scenario, where I subtract corresponding columns in a Year and Quarter?</P><P> </P><P>Also there may be situation later in which Category A can have a year that Category B may <U>not</U> have.</P><P>For example, <U>Category A</U> may have <U><STRONG>2018</STRONG> and 2019</U>, while <U>Category B</U> may have <U>2017 and <STRONG>2018</STRONG></U>.</P><P>In this scenario, I will need the difference between the 2018 Quarters only, don't need any computation on 2017 and 2019.</P><P> </P><P>Any ideas please, using DAX?</P><P> </P>Wed, 30 Dec 2020 11:21:38 GMThttps://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1574358#M640436snph17772020-12-30T11:21:38ZRe: Subtract two corresponding columns in a Power BI matrix: DAX help needed
https://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1574367#M640439
<P><LI-USER uid="243629"></LI-USER> , You can have measure like</P>
<P> </P>
<P>calculate(SUM(Table[Column]), filter(Table, Table[CATEGORY] ="A")) - calculate(SUM(Table[Column]), filter(Table, Table[CATEGORY] ="B"))</P>
<P> </P>
<P>Or measure like this one, where max(year) can be replace with actual year or max(Year)-1 etc</P>
<P>calculate(SUM(Table[Column]), filter(allselected(Table), Table[CATEGORY] ="A" && [year] =max([Year]) && [Qtr] =max([QTR]))) <BR />- calculate(SUM(Table[Column]), filter(allselected(Table), Table[CATEGORY] ="B" && [year] =max([Year]) && [Qtr] =max([QTR])))</P>Wed, 30 Dec 2020 03:31:54 GMThttps://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1574367#M640439amitchandak2020-12-30T03:31:54ZRe: Subtract two corresponding columns in a Power BI matrix: DAX help needed
https://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1574842#M640566
<P>Thanks Amit. Appreciate your help.</P><P> </P><P>There is one more piece of information I need to provide; the Category selection happens via a Slicer.</P><P> </P><P>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.</P><P> </P><P>The slicer will always choose <U>exactly</U> 2 categories at a time.</P><P> </P><P>So, I cannot hard code anything. Everything is dynamic.</P><P> </P><P>Category A may have 2019 and 2018,<BR />while Category B may have 2019 and 2017,<BR />while Category C may have 2019, 2020, 2021, etc.</P><P> </P><P>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).</P><P> </P><P>I think we may need to use some variable to match the year, and then move to quarter, and later to month.</P>Wed, 30 Dec 2020 11:25:19 GMThttps://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1574842#M640566snph17772020-12-30T11:25:19ZRe: Subtract two corresponding columns in a Power BI matrix: DAX help needed
https://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1578842#M641720
<P>Hi, <LI-USER uid="243629"></LI-USER> </P><P>Given my understanding of you need, do you want to calculate variance base on slicer?</P><P>Would you provide sample data and expected output?</P><P>Sample data and expected output would help tremendously.<BR />Please see this post regarding How to Get Your Question Answered Quickly:</P><P><A href="https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FCommunity-Blog%2FHow-to-Get-Your-Question-Answered-Quickly%2Fba-p%2F38490&data=04%7C01%7Cv-xulinchen%40microsoft.com%7C52ac8843906c45af1f4508d8b0881688%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637453445822898373%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=%2BO%2Fel%2FR%2BNY0oZcCeW4lH9H8%2BfUrB%2Fw7VIzWQyei6KLo%3D&reserved=0" target="_blank">https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490</A></P><P>Best Regards,</P><P>Link Chen</P>Mon, 04 Jan 2021 08:12:41 GMThttps://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1578842#M641720v-xulin-mstf2021-01-04T08:12:41ZRe: Subtract two corresponding columns in a Power BI matrix: DAX help needed
https://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1580334#M642194
<P>I figured out:</P><P> </P><P>Variance =</P><P> </P><P>VAR Cat1 = MAXX (ALLSELECTED (Table1[Category]), Table1[Category])<BR />VAR Cat2 = MINX (ALLSELECTED (Table1[Category]), Table1[Category])</P><P> </P><P>VAR Sum1 = CALCULATE (SUM(Table1[Column]), Table1[Category] = Cat1 )<BR />VAR Sum2 = CALCULATE (SUM(Table1[Column]), Table1[Category] = Cat2 )</P><P> </P><P>RETURN<BR />IF (ISBLANK (Sum1) || ISBLANK (Sum2), BLANK(), Sum1 - Sum2 )</P><P> </P><P>Cat1 and Cat2 variables come from the Slicer.</P>Mon, 04 Jan 2021 20:14:49 GMThttps://community.powerbi.com/t5/Desktop/Subtract-two-corresponding-columns-in-a-Power-BI-matrix-DAX-help/m-p/1580334#M642194snph17772021-01-04T20:14:49Z