Forum Discussion

daniel_kusmider's avatar
daniel_kusmider
New Member
4 years ago
Solved

Calculating difference to previous row

Hi,   I'm trying to duplicate in Power BI calculation that's easly done in Excel in a Pivot Table - % diff to previous visible row. In a Pivot Table you can just choose Show Values as % Difference...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, daniel_kusmider 

    Try to create measures like below:

    SUM = SUM('Table'[Leads])
    ThisWeek = 
CALCULATE(
    [SUM],
    FILTER(
        ALLSELECTED('Table'),
        'Table'[End of Week]=MAX('Table'[End of Week])
    )
)
    LastWeek = 
CALCULATE(
    [SUM],
    FILTER(
        ALLSELECTED('Table'),
        'Table'[End of Week]=MAX('Table'[End of Week])-7
    )
)
    LeadDiff% = 
DIVIDE(
    [ThisWeek]-[LastWeek],
    [LastWeek]
)

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

daniel_kusmider's avatar
daniel_kusmider
New Member
4 years ago

Thanks amitchandak,

 

That's a step forward 😉

To what you said I added one measure:

% Leads Diff = IFERROR((Leads[This Week] - Leads[Last Week])/Leads[Last Week], blank())
and this one I showed in a table next to summarized Leads quantity:

 

Now the question is how to make it interactive with slicers filtering the table.

As you can see below when I use any filters/slicers percentage values make no sence because they are calculated based on a total value for that week:

 


 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Share the link from where i can download your PBI file.  Clearly show the problem there.