CODEWITHSUNDEEP
powerbidax
Maria
Maria

Reputation: 35

Calculate row difference in Power Bi

I am looking for help calculating the difference between the previous row, and the % after that in order to built a funnel in Power Bi.

enter image description here

Could you help me?

Upvotes: 3

Views: 3716

Answers (1)

RADO
RADO

Reputation: 8148

I used the following table to prototype a solution:

Table name: "Data"

enter image description here

(column names are slightly different, to make measure naming easier; change them as you prefer).

Create first measure:

Total Amount = SUM(Data[Amount])

Create second measure:

Accumulated Difference =
VAR Current_Index = MAX ( Data[Index] )
VAR Initial_Amount =
    CALCULATE ( 
        MAX ( Data[Amount] ), 
        FILTER ( ALL ( Data ), Data[Index] = 1 ) )
VAR Accumulated_Reductions =
    CALCULATE (
        [Total Amount],
        FILTER ( ALL ( Data ), Data[Index] > 1 && Data[Index] <= Current_Index ) )
RETURN
    Initial_Amount - Accumulated_Reductions

Create third measure:

% Previous =
VAR Current_Index = MAX ( Data[Index] )
VAR Current_Difference =
    CALCULATE (
        [Accumulated Difference],
        FILTER ( ALL ( Data ), Data[Index] = Current_Index ) )
VAR Previous_Difference =
    IF (Current_Index > 1,
        CALCULATE (
            [Accumulated Difference],
            FILTER ( ALL ( Data ), Data[Index] = Current_Index - 1 ) ) )
RETURN
    DIVIDE ( Current_Difference, Previous_Difference )

Result:

enter image description here

Explanation:

First measure is simply for convenience, to avoid writing the same summation multiple times.

Second measure: First, we find index visible in a current row and save it in a variable. Then, we find the initial amount (amount where index = 1), because we need it as a starting point. ALL is necessary to ignore filters applied to the row. Then, using a similar pattern, we calculate accumulated reductions between current and the initial rows (for example, for step D, we need to sum amounts for indexes (4, 3, 2)) Finally, the desired result is simply initial amount - accumulated reductions.

Third measure: Using very similar technique, we first find Accumulated Difference for the current row, then for the previous row. The only nuance here is to test previous row for the starting conditions (if it's the initial row with index 1, don't calculate previous values). Once current and previous differences are known, we simply divide them.

Upvotes: 4

Related Questions