Forum Discussion

ganeshnr073's avatar
ganeshnr073
Helper I
3 years ago
Solved

Percentage difference

Hi

 

Need help to calculate the percentage differnce bwtween dates, with refrence to the Happy% column in the table. And based on increase or decarese need to show a visual if it's dropped or increased.

 

Thanks

 

 

Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    3 years ago

    Hi ganeshnr073 

    You can achieve this by creating a calculated column 

    PercentageDifference =
    VAR CurrentRowDate = 'YourTable'[Date]
    VAR PreviousRowDate =
    CALCULATE(
    MAX('YourTable'[Date]),
    FILTER('YourTable', 'YourTable'[Date] < CurrentRowDate)
    )
    VAR CurrentRowHappy = 'YourTable'[Happy%]
    VAR PreviousRowHappy =
    CALCULATE(
    MAX('YourTable'[Happy%]),
    FILTER('YourTable', 'YourTable'[Date] = PreviousRowDate)
    )
    RETURN
    IF(
    ISBLANK(PreviousRowHappy),
    BLANK(),
    DIVIDE(CurrentRowHappy - PreviousRowHappy, PreviousRowHappy)
    )

     

    Thn create a other column 

    Change =
    SWITCH(
    TRUE(),
    'YourTable'[PercentageDifference] > 0, "Increased",
    'YourTable'[PercentageDifference] < 0, "Decreased",
    "No Change"
    )

    This formula uses the SWITCH function to categorize the percentage difference as "Increased" if it's greater than 0, "Decreased" if it's less than 0, and "No Change" if it's 0

    Thank you. Hope this will help

1 Reply

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    3 years ago

    Hi ganeshnr073 

    You can achieve this by creating a calculated column 

    PercentageDifference =
    VAR CurrentRowDate = 'YourTable'[Date]
    VAR PreviousRowDate =
    CALCULATE(
    MAX('YourTable'[Date]),
    FILTER('YourTable', 'YourTable'[Date] < CurrentRowDate)
    )
    VAR CurrentRowHappy = 'YourTable'[Happy%]
    VAR PreviousRowHappy =
    CALCULATE(
    MAX('YourTable'[Happy%]),
    FILTER('YourTable', 'YourTable'[Date] = PreviousRowDate)
    )
    RETURN
    IF(
    ISBLANK(PreviousRowHappy),
    BLANK(),
    DIVIDE(CurrentRowHappy - PreviousRowHappy, PreviousRowHappy)
    )

     

    Thn create a other column 

    Change =
    SWITCH(
    TRUE(),
    'YourTable'[PercentageDifference] > 0, "Increased",
    'YourTable'[PercentageDifference] < 0, "Decreased",
    "No Change"
    )

    This formula uses the SWITCH function to categorize the percentage difference as "Increased" if it's greater than 0, "Decreased" if it's less than 0, and "No Change" if it's 0

    Thank you. Hope this will help