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

[Anonymous]

[AAspa]

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

Many thanks

[tamerj1]

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 

[Anonymous]

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 .

[tamerj1]

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. 

[Anonymous]

 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 

[tamerj1 · Accepted solution]

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

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 

[Anonymous]

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

[Anonymous]

Sample solution as per your requirement.

Attached .pbix file.

https://nicesoftwaresolutions1-my.sharepoint.com/:u:/g/personal/ameshram_nicesoftwaresolutions_com/E...

Regards,

Aditya

[Anonymous]

Sample solution as per your requirement.

Attached .pbix file.

https://nicesoftwaresolutions1-my.sharepoint.com/:u:/g/personal/ameshram_nicesoftwaresolutions_com/E...

Regards,

Aditya

[nickyvv]

@Anonymous  @ibarrau thread moved to DAX forum

[ibarrau]

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,

[Anonymous]

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

[ibarrau]

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

[Anonymous]

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 .