Forum Discussion

VikramAdi's avatar
VikramAdi
Helper II
3 years ago

Exclude specific slicer value from Measure

Need to calculate Growth from Prior period and display in Matrix table.

Sliceres:  Scenario  (Actual, budget, Fore cast), Period, Year.

 

If i select scenario as actual, below formula should work, now it's working fine

Growth from Prior period = ((actual Amount - Prior period Actual Amount) / Prior period Actual Amount),

 

 

If i select scenario as Budget, below formula should work, 

Growth from Prior period = ((Budget Amount - Prior period Actual Amount) / Prior period Actual Amount)

when i chose Scenario as budget , Prior period Actual Amount is  zero, because of Scenario filter applied as bidget.

 

So can anyone help me my formula should work , when i chose scenario as budget, my measure should pick 'Prior period Actual Amount' based on period and Year slicer.

 
 [NetRev]=CALCULATE(SUM('table'[Amount]),FILTER('table','table'[Account]="NetRev" )
 
Net Revenue Actual = CALCULATE(CALCULATE(SUM('table'[Amount]),FILTER('table','table'[Account]="NetRev" && 'table[Scenario]="Actual")),REMOVEFILTERS('table'[Scenario]))
 
Growth from prior period Actual = CALCULATE(CALCULATE([Net Revenue Actual] ,SAMEPERIODLASTYEAR('Date'[Date])),REMOVEFILTERS('table'[Scenario]))

 

Growth from prior period % = DIVIDE(([NetRev]-[Growth from prior period Actual]),[Growth from prior period Actual],0)

 

Can anyone help me here my measure should pick up period, year slicer and exclude scenario slicer . 

 

Thanks,

Need Help

1 Reply

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    3 years ago

    Hello VikramAdi ,

    Can you please try using ALL instead of REMOVEFILTERS and using ALL is bit different than REMOVEFILTERS so go through it and see if your requirement is fulfilled or not. 

     

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