Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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

     

ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      ibarrau
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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 .