Forum Discussion

mohd89ali's avatar
mohd89ali
Frequent Visitor
6 years ago
Solved

increase/decrease difference percentage

Hi all, 

 

i am working on a COVID-19 dashboard and i am facing a problem calculating the increase/decrease in the difference by percent. 

 

look at the table below 

 

Country/RegionProvince/StateDateCase_TypeCasesLongLatDifferenceLast_Update_Date
ChinaAnhui2020-01-22Deaths0117.226431.825702020-03-15 12:54:41
ChinaAnhui2020-01-23Deaths0117.226431.825702020-03-15 12:54:41
ChinaAnhui2020-01-24Deaths0117.226431.825702020-03-15 12:54:41
ChinaAnhui2020-01-22Confirmed1117.226431.825712020-03-15 12:54:41
ChinaAnhui2020-01-23Confirmed9117.226431.825782020-03-15 12:54:41
ChinaAnhui2020-01-24Confirmed15117.226431.825762020-03-15 12:54:41

 

i want to calculate the increase and decrease in percentage day by day to look like this

 

 

Country/RegionProvince/StateDateCase_TypeCasesLongLatDifferenceincrease/decrease difference in percentageLast_Update_Date
ChinaAnhui2020-01-22Deaths0117.226431.8257102020-03-15 12:54:41
ChinaAnhui2020-01-23Deaths0117.226431.82572100%2020-03-15 12:54:41
ChinaAnhui2020-01-24Deaths0117.226431.82578300%2020-03-15 12:54:41
ChinaAnhui2020-01-22Confirmed1117.226431.8257102020-03-15 12:54:41
ChinaAnhui2020-01-23Confirmed9117.226431.82578700%2020-03-15 12:54:41
ChinaAnhui2020-01-24Confirmed15117.226431.82576-25%2020-03-15 12:54:41

 

could you help me with that please? 

 

 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try this measure:

    Measure = 
VAR a =
    IF (
        MAX ( 'Table'[Difference] )
            = MINX ( ALLEXCEPT ( 'Table', 'Table'[Case_Type] ), 'Table'[Difference] ),
        1,
        CALCULATE (
            MAX ( 'Table'[Difference] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Case_Type] IN FILTERS ( 'Table'[Case_Type] )
                    && 'Table'[Date] < MAX ( 'Table'[Date] )
            )
        )
    )
RETURN
    ( MAX ( 'Table'[Difference] ) - a ) / a

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi mohd89ali ,

     

    How are you making the calculation for increase/decrease not really sure how you are achiveing the values, made several try but cannot get the same values.

     

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try this measure:

    Measure = 
VAR a =
    IF (
        MAX ( 'Table'[Difference] )
            = MINX ( ALLEXCEPT ( 'Table', 'Table'[Case_Type] ), 'Table'[Difference] ),
        1,
        CALCULATE (
            MAX ( 'Table'[Difference] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Case_Type] IN FILTERS ( 'Table'[Case_Type] )
                    && 'Table'[Date] < MAX ( 'Table'[Date] )
            )
        )
    )
RETURN
    ( MAX ( 'Table'[Difference] ) - a ) / a

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi