topic Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi in DAX Commands and Tips

How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

Anonymous — Mon, 07 Feb 2022 10:00:06 GMT

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

ibarrau — Mon, 07 Feb 2022 12:20:28 GMT

Hi. I think this is a question for the DAX forum. Anyway. In this case, you have years. That might help you to create a math subsctracting current selection and last year. You can do a measure like this:

Measure = _current = CALCULATE ( SUM(Table[Value]) ) _last_year = CALCULATE ( SUM(Table[Value]) , SAMEPERIODLASTYEAR(Calendar[Date]) ) RETURN _current - _last_year

In order to make that calculation you need a calendar or date table to help you get time intelligence calculations. That would be the best practice, but if you don't like the idea you can use SELECTEDVALUE to get the current year and just do -1 in the _last_year filter context instead of SAMEPERIODLASTYEAR. That way when you filter a year you can get current - last difference. If you don't filter it will try to render differences with last year for each year in the matrix. The measure won't make sense if you add it in a visual without a period of time.

I hope that helps,

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

Anonymous — Wed, 09 Feb 2022 11:10:31 GMT

Thanks for your response Ibbarau ,
My values are actually codes ( like m124 , b016, cd56 ) . I categorized them as Distinct count . so when I use SUM the DAX is not working.
If you need any info. Please ask .
Thanks

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

nickyvv — Wed, 09 Feb 2022 11:27:20 GMT

Anonymous ibarrau thread moved to DAX forum

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

ibarrau — Wed, 09 Feb 2022 12:57:20 GMT

Ok. If the numbers in the fields are distinct count of categories you can replace the SUM of the measure I have sent with DISTINCTCOUNT function in DAX.

I hope that work

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

Anonymous — Wed, 09 Feb 2022 15:44:53 GMT

Thanks for your solution
I also wrote one dax it worked out

Measure =

VAR X = CALCULATE(DISTINCTCOUNT(table[ Code]),FILTER(table,table[Year]=MIN(table[Year])))

var y =CALCULATE(DISTINCTCOUNT(table[Code]),FILTER(table , table[Year]=MAX(table[Year])))

Return

y-X
but the same dax is not working for calculated column. Why . Can you explain
can you write suitable Dax for calculated column .

Thank you so much .

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

Anonymous — Wed, 09 Feb 2022 16:07:05 GMT

Sample solution as per your requirement.

Attached .pbix file.

https://nicesoftwaresolutions1-my.sharepoint.com/:u:/g/personal/ameshram_nicesoftwaresolutions_com/EYiLgDmP6YxLnxukC0TutVsB1Hx9Q6e5-WRYOpJC4cOxvA?e=Tee2b

Regards,

Aditya

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

Anonymous — Wed, 09 Feb 2022 16:07:11 GMT

Sample solution as per your requirement.

Attached .pbix file.

https://nicesoftwaresolutions1-my.sharepoint.com/:u:/g/personal/ameshram_nicesoftwaresolutions_com/EYiLgDmP6YxLnxukC0TutVsB1Hx9Q6e5-WRYOpJC4cOxvA?e=Tee2b

Regards,

Aditya

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

tamerj1 — Wed, 09 Feb 2022 16:21:07 GMT

Hi Anonymous

Check out this sample file https://www.dropbox.com/t/34kVadAA9Yb40CUO
Actually, you cannot add a new measure to the matrix visual. If you do so you will see two values under each year. Therefore, the only choice is to create a new measure that is built on top of the original one, playing with the code in order to view the difference at the Total column like this

This is the code: Note: [Measure] is your original measure

Measure1 = VAR MaxYear = MAX ( 'Table'[Year] ) VAR MinYear = MIN ( 'Table'[Year] ) VAR MaxYearValue = CALCULATE ( [Measure], 'Table'[Year] = MaxYear ) VAR MinYearValue = CALCULATE ( [Measure], 'Table'[Year] = MinYear ) VAR Difference = MaxYearValue - MinYearValue VAR Result = SWITCH ( TRUE, NOT HASONEVALUE ( 'Table'[Year] ), Difference, COUNTROWS ( ALLSELECTED ('Table'[Year] ) ) = 1, BLANK(), [Measure] ) RETURN Result

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

Anonymous — Wed, 09 Feb 2022 18:00:23 GMT

Thanks For Your Solution .
One thing is the values column in your Pbix is numbers like 10 , 20 26 ,but my requirement is, values column is codes like b010 , c350, d6f , ng6 . I categorized them as Distinct count . So they look like numbers in my attached file . I think the DAX is almost correct . Can you modify dax by using my scenario .
Thanks For such great Help .

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

tamerj1 — Wed, 09 Feb 2022 18:09:02 GMT

You can just refer your measure name instead of [Measure] and it should work. Otherwise, you can send your file with a sample data and I'll do it for you.
If you are satisfied please mark my reply as accepted solution. Kudoes are also appreciated.

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

Anonymous — Wed, 09 Feb 2022 19:00:18 GMT

I replaced values with codes . My requirement is look like this . I categorized them as distinct count ( from column tools ) .What I want is exactly like your output . Measure 1 is exactly corect . I think we need to modify the Measure , with codes (Distinct Count ) .Please modify the dax .,It would work fine. Am unable tho send the file .
Kudos !
Thanks for such quick response

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

tamerj1 — Thu, 10 Feb 2022 01:13:15 GMT

Hi Anonymous
Here you go https://www.dropbox.com/t/sMyzwonHckNHvdKX
Original Measure code remains the same

Measure = COUNTROWS ( DISTINCT ('Table'[Value] ) )

New Measure code is

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

Anonymous — Thu, 10 Feb 2022 04:31:25 GMT

Thank You So Much Tamerj1 .
You deserve to be a super user .
Kudos ! Kudos !

Re: How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

AAspa — Wed, 27 Nov 2024 12:00:18 GMT

is there a way to add an additional column with the difference in percentage?

Many thanks