CODEWITHSUNDEEP
powerbidaxpercentagemetricscalculated-columns
jessirocha
jessirocha

Reputation: 537

Calculate percentage difference between consecutive rows - Power BI

I have a table in the following structure:

Forecast Type     Group     Forecast 
June 00             A          120
July 01             A          125
June 00             B          250
July 01             B          222
June 00             C          308
June 01             C          270

I would like to visualize this in a matrix by grouping by "Forecast Type" and aggregate by summing the "Forecast". Then, I would like to create a calculated column or metric that contains the percentage difference between the Forecast Type. If the user filter by "Group" using the Slicer, this should also be updated accordingly. This is what the matrix should look like when all groups are selected:

                  Forecast      Difference in EUR      Percentage Difference
June 00           678                  null                        null            
July 01           617                  -61                          -9%

I was able to create the "Difference in EUR" variable with some research by creating an Index column and using the:

Difference in EUR = 
VAR RowAbove=
    CALCULATE (
        SUM ('Forecast Development'[Forecast]),
            FILTER ('Forecast Development', 'Forecast Development'[Index] = EARLIER ('Forecast Development'[Index]) -1))
    RETURN
    'Forecast Development'[Forecast] - RowAbove

However, I am not able to create the percentage and by using this calculated column formula. Also, in the "Difference in EUR" column, the first row is not null, as it should be since I dont have another value to calculate the difference with. I also tried to force the first row to being null by creating another calculated column and filtering out the "Forecast Type" June 00 but apparently I can not do this.

I am also not sure how to create the index, for now I just did normally from 1 to 6. But maybe I need to restart the index everytime a group changes? So, 1,2,1,2,1,2?

I am new in Power BI and would appreciate the help. Thanks!

Upvotes: 0

Views: 1677

Answers (1)

msta42a
msta42a

Reputation: 3741

If your [Forecast Type] always contain valid number at the end then we can calculate this like that:

Diff = 
var __CurrentType = value( RIGHT(SELECTEDVALUE(Sheet1[Forecast Type]),2))
var _prevForecast = CALCULATE( sum(Sheet1[Forecast]), filter(all(Sheet1[Forecast Type]), value( RIGHT(Sheet1[Forecast Type],2)) = __CurrentType -1 ))
var __currForecast = CALCULATE(sum(Sheet1[Forecast]))
return
if (ISBLANK(_prevForecast), BLANK(),
__currForecast - _prevForecast)


Percentage%% = 
var __CurrentType = value( RIGHT(SELECTEDVALUE(Sheet1[Forecast Type]),2))
var _prevForecast = CALCULATE( sum(Sheet1[Forecast]), filter(all(Sheet1[Forecast Type]), value( RIGHT(Sheet1[Forecast Type],2)) = __CurrentType -1 ))
var __currForecast = CALCULATE(sum(Sheet1[Forecast]))
return
DIVIDE(__currForecast, _prevForecast)

enter image description here enter image description here

Upvotes: 1

Related Questions