Show deviations in matrix between years

[Peavey]

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-

[v-henryk-mstf]

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]

@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 · Accepted solution]

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.

[Peavey]

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-

[Ashish_Mathur]

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.

[S_JB]

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]

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-