Forum Discussion

Ahaconsulting's avatar
Ahaconsulting
New Member
4 years ago
Solved

Calculate difference in percentage

Hello, 

 

How can I calculate the difference between 2 percentages?

Example of my data:

 

 

Ex 'Raw materials':

=> Raw materials = Actuals if subcategory is 'raw materials'

2021: €1 061 204

2022: €1 634 786

=> Raw materials vs sales % = divide(raw materials, sales)

2021: 28,01%

2022: 34,13%

 

Now I want to know the difference between the 'raw materials vs sales %'.

So, 34,13% - 28,01% = 6,12%

 

Which formula can I use?

 

Thanks.

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Ahaconsulting ,

    You could try it.

    %per =
VAR _per =
    SUM ( [Value] )
        / CALCULATE (
            SUM ( [Value] ),
            FILTER ( ALL ( 'Table' ), [year] = MAX ( [year] ) && 'Table'[Cate] = "Sales" )
        )
RETURN
    IF (
        HASONEFILTER ( 'Table'[year] ),
        _per,
        CALCULATE ( SUM ( [Value] ), FILTER ( 'Table', [year] = 2022 ) )
            / CALCULATE (
                SUM ( [Value] ),
                FILTER ( ALL ( 'Table' ), 'Table'[year] = 2022 && [Cate] = "Sales" )
            )
            - CALCULATE ( SUM ( [Value] ), FILTER ( 'Table', [year] = 2021 ) )
                / CALCULATE (
                    SUM ( [Value] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[year] = 2021 && [Cate] = "Sales" )
                )
    )

    The final show:

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Ahaconsulting , you can create measure for this year vs last year and take diff

     

    example measure using date table

     

    This Year Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date],1,Year), "7/31")

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

     

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

  • Ahaconsulting's avatar
    Ahaconsulting
    New Member
    4 years ago

    Hello Amit ,

     

    Thank you, but this doesn't work for my report.

    Reason:

    If I select the 2022, I get the actuals of 2022 and the year before, 2021

    When I select the 2021, I get 2021 and 2020

     

    Formula I Used to become this: Measure PrevYear Check = IF(MAX(Calender[Year])>(MAX(YearKey[Year])-2) && MAX(Calender[Year])<=MAX(YearKey[Year]),1,0)
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Ahaconsulting ,

    You could try it.

    %per =
VAR _per =
    SUM ( [Value] )
        / CALCULATE (
            SUM ( [Value] ),
            FILTER ( ALL ( 'Table' ), [year] = MAX ( [year] ) && 'Table'[Cate] = "Sales" )
        )
RETURN
    IF (
        HASONEFILTER ( 'Table'[year] ),
        _per,
        CALCULATE ( SUM ( [Value] ), FILTER ( 'Table', [year] = 2022 ) )
            / CALCULATE (
                SUM ( [Value] ),
                FILTER ( ALL ( 'Table' ), 'Table'[year] = 2022 && [Cate] = "Sales" )
            )
            - CALCULATE ( SUM ( [Value] ), FILTER ( 'Table', [year] = 2021 ) )
                / CALCULATE (
                    SUM ( [Value] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[year] = 2021 && [Cate] = "Sales" )
                )
    )

    The final show:

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.