Forum Discussion

rkapoor's avatar
rkapoor
New Member
3 years ago

subtracting 2 rows with a condition

Hi Everyone,

 

I have the following dataset where I want to subtract the sum of the Forecasting from the sum of Forecast Revision 1 and so on. How can I do it? Please help.

 

 

Need Help

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi rkapoor,

     

    Based on your description, I have created a simple sample:

    Please try:

    First created a new column:

    Rank = IF([Value Type Name]="Forecasting",0,VALUE(RIGHT([Value Type Name],LEN([Value Type Name])-LEN("Forecast Revision "))))

    Output:

    Then apply the measure:

    Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Rank]=MAX('Table'[Rank])))-CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Rank]<MAX('Table'[Rank])))

    Final output:

    Best Regards,

    Jianbo Li

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