Forum Discussion

Peavey's avatar
Peavey
Helper III
5 years ago
Solved

Show deviations in matrix between years

Hi,

 

I want to show deviation between 2 years in a matrix visual. How can I show deviation by subtracting or other good ideas in Desktop PBI? 

 

Item      2021      2022      Deviation

a           1000      1500           ?

b           2500      2500           ?

c           4000      3000           ?

 

I think what I am looking for is answered here: 

https://community.powerbi.com/t5/Desktop/DAX-Formula-for-subtracting-columns/m-p/226534#M100859

 

But I don't get where "('EKRN-a482911f-cdbf-4b5f-9e2d-8cde02e7f164-31_05_2017_clean july 2017'[species-count]))" comes from?

 

Thanks

-A-

Need Help
Show and Tell
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi Peavey ,

     

    Based on the above error, you can create the following measure, replacing the field originally placed in value and the created Diff 2022-2021 measure.

    Diff 2022-2021 = 
IF(HASONEFILTER('Table'[year]),MAX('Table'[amount]),CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER ( ALL ( 'Table'), 'Table'[year] = 2021 )
)
    - CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[year] = 2022 )
    ))

     


    Best Regards,
    Henry

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • S_JB's avatar
    S_JB
    Resolver III
    5 years ago

    This looks like it could be the data table name in the sample PBIX file that was sent. The DAX references the data table then specificies the measure to use.

     

    • Peavey's avatar
      Peavey
      Helper III
      5 years ago

      Same as I guessed, but I don't seem to understand the DAX here. Can someone please show me in my example what to do? Maybe I get it then 🙂

       

      Thanks

      -A-

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    You must first create a Calendar Table with a relationship to your Data Table.  In the Calendar Table, write calculated column formulas to extract Years.  To your visual, drag Years from the Calendar Table.  Thereafter you will be able to write measures.

  • Peavey's avatar
    Peavey
    Helper III
    5 years ago

    I got it to work with this formula:

    --------------------------------
    Diff 2022-2021 =
    CALCULATE(
    SUM(ViewBudget[Amount]),
    FILTER(
    ALL(ViewBudget[Year]),
    ViewBudget[Year] = 2021
    )
    )
    - CALCULATE(
    SUM(ViewBudget[Amount]),
    FILTER(
    ALL(ViewBudget[Year]),
    ViewBudget[Year] = 2022
    )
    )
    ------------------------------
    But the difference column appears two times.... Any idea if this can displyed be only once?
     
    -A-
  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi Peavey ,

     

    According to your doubts, in the above solution, the two formulas are the total value of the [species-count] field and the total value of the [action-taken] field is empty.

     

    You can try the following formula:

    Diff 2022-2021 =
CALCULATE (
    SUM ( ViewBudget[Amount] ),
    FILTER ( ALL ( 'ViewBudget' ), ViewBudget[Year] = 2021 )
)
    - CALCULATE (
        SUM ( ViewBudget[Amount] ),
        FILTER ( ALL ( 'ViewBudget' ), ViewBudget[Year] = 2022 )
    )

     

    If the problem is still not resolved, please provide detailed error information and test data model. I will answer for you as soon as possible.

     

    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Peavey's avatar
      Peavey
      Helper III
      5 years ago

      v-henryk-mstf Thanks! 

      Yes this solution works, but when I add the new measure to the matrix I get the new measure calculation in two columns. Any idea how I can display it only once?

       

      -A-

      • v-henryk-mstf's avatar
        v-henryk-mstf
        Community Support
        5 years ago

        Hi Peavey ,

         

        Based on the above error, you can create the following measure, replacing the field originally placed in value and the created Diff 2022-2021 measure.

        Diff 2022-2021 = 
IF(HASONEFILTER('Table'[year]),MAX('Table'[amount]),CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER ( ALL ( 'Table'), 'Table'[year] = 2021 )
)
    - CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[year] = 2022 )
    ))

         


        Best Regards,
        Henry

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.