Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Slicer selection changes the cumulative total

Hi All,

 

I have a measure which calculates cumulative loss over development years (which is also a measure based on start year and performance month). I used the following the DAX to calculate AnnualDevelopmenDisplay (calcualted from Annual Development) and the CumulativeNetCollateralLoss.

 

AnnualDevelopment = iF(dATEDIFF(FactCRTLoanPerformanceMonth[DimDeal.StartDate],FactCRTLoanPerformanceMonth[PerformanceMonth],YEAR)<=0,0,DATEDIFF(FactCRTLoanPerformanceMonth[DimDeal.StartDate],FactCRTLoanPerformanceMonth[PerformanceMonth],YEAR))
 
AnnualDevelopmentDisplay = If(FactCRTLoanPerformanceMonth[AnnualDevelopment]=0,"0",If(FactCRTLoanPerformanceMonth[AnnualDevelopment]=1,"0-1",iF(FactCRTLoanPerformanceMonth[AnnualDevelopment]=2,"1-2",If(FactCRTLoanPerformanceMonth[AnnualDevelopment]=3,"2-3",If(FactCRTLoanPerformanceMonth[AnnualDevelopment]=4,"3-4",If(FactCRTLoanPerformanceMonth[AnnualDevelopment]=5,"4-5",if(FactCRTLoanPerformanceMonth[AnnualDevelopment]=6,"5+")))))))

  

CumulativeNetCollateralLoss = CALCULATE(SUM(FactCRTLoanPerformanceMonth[CreditEventNetLossAmount]),FILTER(ALLSELECTED(FactCRTLoanPerformanceMonth[AnnualDevelopmentDisplay]),ISONORAFTER(FactCRTLoanPerformanceMonth[AnnualDevelopmentDisplay],Max(FactCRTLoanPerformanceMonth[AnnualDevelopmentDisplay]),DESC)))
 
I get the right numbers with this DAX formula. However, when the slicer is used for Annual Development Display selection, the Cumulative value is un-cumulated. Please see the following image for the issue description.
 

Slicer unselectedSlicer selected
I expect the the slicer selection to display the same value as it's in the final table.
Please let me know if I am missing something here, I am new to PowerBi and DAX. Your help will be highly appreciated.
 
Please see the sample DATA below (filtered by one deal, as per the report criteria). Please note that CreditEventNetLossAmount is the calculated column.
DimDeal.StartDateDealIDPerformanceMonthCreditEventNetLossAmount
2/26/2014323/1/20140
2/26/2014324/1/20140
2/26/2014325/1/20140
2/26/2014326/1/20140
2/26/2014327/1/201461806
2/26/2014328/1/201412534
2/26/2014329/1/201493461
2/26/20143210/1/2014174623
2/26/20143211/1/2014119235
2/26/20143212/1/2014177913
2/26/2014321/1/201581489
2/26/2014322/1/2015249500
2/26/2014323/1/201588451
2/26/2014324/1/2015116480
2/26/2014325/1/201591149
2/26/2014326/1/2015223681
2/26/2014327/1/201520611
2/26/2014328/1/2015107788
2/26/2014329/1/2015219877
2/26/20143210/1/201565134
2/26/20143211/1/2015176428
2/26/20143212/1/2015194564
2/26/2014321/1/2016276480
2/26/2014322/1/2016205944
2/26/2014323/1/2016370500
2/26/2014324/1/201651543
2/26/2014325/1/2016169476
2/26/2014326/1/2016268503
2/26/2014327/1/2016328455
2/26/2014328/1/2016216249
2/26/2014329/1/2016186896
2/26/20143210/1/2016279587
2/26/20143211/1/2016220184
2/26/20143212/1/2016203915
2/26/2014321/1/2017150670
2/26/2014322/1/2017344587
2/26/2014323/1/2017443104
2/26/2014324/1/2017176915
2/26/2014325/1/2017195982
2/26/2014326/1/2017155957
2/26/2014327/1/2017130947
2/26/2014328/1/2017253935
2/26/2014329/1/2017167044
2/26/20143210/1/2017271076
2/26/20143211/1/2017239677
2/26/20143212/1/2017190364
2/26/2014321/1/2018258930
2/26/2014322/1/2018138391
2/26/2014323/1/2018162087
2/26/2014324/1/2018475329
2/26/2014325/1/2018513233
2/26/2014326/1/2018173594
2/26/2014327/1/2018261316
2/26/2014328/1/2018182793
2/26/2014329/1/2018117470
2/26/20143210/1/2018117017
2/26/20143211/1/2018261685
2/26/20143212/1/2018153336
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    I have use this formula to tested in directquery, it works well

    CALCULATE(SUM('Import for sample Market Risk'[CreditEventNetLossAmount]),FILTER(ALL('Import for sample Market Risk'[DevelopmentAnnualDisplay]),ISONORAFTER([DevelopmentAnnualDisplay],Max([DevelopmentAnnualDisplay]))))
     
    and this formula shouldn't have the limitation in directquery
    If you could try this formula:
    CALCULATE(SUM('Import for sample Market Risk'[CreditEventNetLossAmount]),FILTER(ALL('Import for sample Market Risk'),'Import for sample Market Risk'[DevelopmentAnnualDisplay]>=MIN('Import for sample Market Risk'[DevelopmentAnnualDisplay])))
    or 
    CALCULATE(SUM('Import for sample Market Risk'[CreditEventNetLossAmount]),FILTER(ALL('Import for sample Market Risk'[DevelopmentAnnualDisplay]),'Import for sample Market Risk'[DevelopmentAnnualDisplay]>=MIN('Import for sample Market Risk'[DevelopmentAnnualDisplay])))
    And if still has the problem, please check that when change directquery to import in power bi desktop, it will work.
    In the lower right corner of power bi desktop, then click as below:
     

    Best Regards,

    Lin

     
     

     

9 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous 

    Just use ALL instead of ALLSELECTED for the cumulative total measure.

    Measure =
CALCULATE (
    SUM ( FactCRTLoanPerformanceMonth[CreditEventNetLossAmount] ),
    FILTER (
        ALL ( FactCRTLoanPerformanceMonth[AnnualDevelopmentDisplay] ),
        ISONORAFTER (
                FactCRTLoanPerformanceMonth[AnnualDevelopmentDisplay], MAX ( FactCRTLoanPerformanceMonth[AnnualDevelopmentDisplay] ), DESC
        )
    )
)

    Result:

    Best Regards,

    Lin

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      HI v-lili6-msft 

       

      Thank you very much for replying, I tried using ALL as you suggested below. Unfortunately, it's still not working in my case. I am attaching the screenshots here for results with the measure as per your instruction and my previous measure.Without slicer selectionWith Slicer Selection

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Please let me kow if there is any other workaround to get that cumulative number at the end of the each year (cumulative value carried forward to the next year from the previous year). I have tried to use one more measure (CumulativeLosses, DAX below) which is based on perfromance month not the annualDevelopmentDisplay. It shows the the right cumulative values but my next step would be to just get the cumulative value at the end of each year (December values, as highlighted in the image below).

        CumulativeLosses =
        CALCULATE(SUM(FactCRTLoanPerformanceMonth[CreditEventNetLossAmount]),filter(ALL(FactCRTLoanPerformanceMonth[PerformanceMonth]),FactCRTLoanPerformanceMonth[PerformanceMonth]<=max(FactCRTLoanPerformanceMonth[PerformanceMonth])))