Forum Discussion

Gpera's avatar
Gpera
Regular Visitor
2 years ago
Solved

Table percentage between columns

 

Hi, 

I would like to add per each row the percentage difference from previous year and even possibility to hide and show quarter from this matrix, so that utomatically calculate percentage difference from total between years

So if quarter is seleceted i would like to see for the first row

Country  202120222023
  BudgetDeltaBudgetDeltaBudgetDelta
HungaryQ148526    83935   +72.9%69743-16.90%

 

Thanks in advanced

Need Help
Tips and Tricks
Tutorial Requests
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    Hi Gpera ,

     

    Based on the sample and description you provided, Please try code as below to create a measure.

    Change Percentage = 
VAR _pre = CALCULATE(SUM('Table'[Budget]),
                     FILTER(ALLSELECTED('Table'),
                     [Country]=MAX('Table'[Country])&&[Quarter Q]=MAX('Table'[Quarter Q])&&[Year]=MAX('Table'[Year])-1)
                    )
VAR _pre2 = CALCULATE(SUM('Table'[Budget]),
                    FILTER(ALLSELECTED('Table'),
                    [Country]=MAX('Table'[Country])&&[Year]=MAX('Table'[Year])-1)
                    )
VAR _cur = SUM('Table'[Budget])
RETURN IF(ISINSCOPE('Table'[Quarter Q]),
            DIVIDE(_cur-_pre,_pre),
            DIVIDE(_cur-_pre2,_pre2)
        )

    Result is as below.

     

    Best Regards,
    Yulia Yan

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

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Gpera 

    Try this custom visual, it can be highly customized to suit your needs:

     

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Community Support
    2 years ago

    Hi Gpera ,

     

    Based on the sample and description you provided, Please try code as below to create a measure.

    Change Percentage = 
VAR _pre = CALCULATE(SUM('Table'[Budget]),
                     FILTER(ALLSELECTED('Table'),
                     [Country]=MAX('Table'[Country])&&[Quarter Q]=MAX('Table'[Quarter Q])&&[Year]=MAX('Table'[Year])-1)
                    )
VAR _pre2 = CALCULATE(SUM('Table'[Budget]),
                    FILTER(ALLSELECTED('Table'),
                    [Country]=MAX('Table'[Country])&&[Year]=MAX('Table'[Year])-1)
                    )
VAR _cur = SUM('Table'[Budget])
RETURN IF(ISINSCOPE('Table'[Quarter Q]),
            DIVIDE(_cur-_pre,_pre),
            DIVIDE(_cur-_pre2,_pre2)
        )

    Result is as below.

     

    Best Regards,
    Yulia Yan

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