Forum Discussion

MIkeMak's avatar
MIkeMak
Regular Visitor
9 years ago
Solved

Incremental percent

 

How do I calculate the column "incremental percent" in Power BI for example  for column D1 the formula is =+C1/B1-1 ,

the  formula for column D2  is =+SUM(C1:C2)/SUM(B1:B2)-1 and the formula for column D3 is +SUM(C1:C3)/SUM(B1:B3)-1 and so on , than you

 

  

     A                            B                                         C                            D

   Month                  sales last year                   sales current year      Incremental percent 

1   January                   5.484.136                          5.882.299                             7.3%

2   February                 5.805.312                          6.692.629                            11.4%

3  March                      5.603.781                          6.831.526                            14.9% 

 

  • mattbrice's avatar
    mattbrice
    9 years ago

     

     

    Actually, after thinking about it, I think the VAR will be out of scope for the FILTER, so need to change this to:

     

     

    measure =
CALCULATE (
    DIVIDE ( SUM ( Table[C] ), SUM ( Table[B] ) ) - 1,
    FILTER (
        ALL ( Calendar ),
        Calendar[MonthNumber] <= MAX ( Calendar[MonthNumber] )
    )
)

     

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi MIkeMak,

    Please try the measure like mattbrice posted. Set the measure data type as percentage.




    Then create a table visual, select the month, sales last year sales, current year sales and measure as value, you will get expected result.



    Best Regards,
    Angelia 

3 Replies

  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    9 years ago

     

     

    Actually, after thinking about it, I think the VAR will be out of scope for the FILTER, so need to change this to:

     

     

    measure =
CALCULATE (
    DIVIDE ( SUM ( Table[C] ), SUM ( Table[B] ) ) - 1,
    FILTER (
        ALL ( Calendar ),
        Calendar[MonthNumber] <= MAX ( Calendar[MonthNumber] )
    )
)

     

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi MIkeMak,

    Please try the measure like mattbrice posted. Set the measure data type as percentage.




    Then create a table visual, select the month, sales last year sales, current year sales and measure as value, you will get expected result.



    Best Regards,
    Angelia 

    • MIkeMak's avatar
      MIkeMak
      Regular Visitor
      9 years ago

      Thank you!  v-huizhn-msft and  mattbrice for the solutions , best regards