Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 unselectedSlicer selectedSlicer 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
1 ACCEPTED SOLUTION

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:
2.JPG
 

Best Regards,

Lin

 
 

 

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

View solution in original post

9 REPLIES 9
v-lili6-msft
Community Support
Community Support

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:

12.JPG

13.JPG

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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 selectionWithout slicer selectionWith Slicer SelectionWith Slicer Selection

Anonymous
Not applicable

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])))
 
3.PNG

hi, @Anonymous 

1. when you use all function, the result should not be affected by all(...), are that attribute column is from different table, could you share your sample pbix file?

2. If you need to add a year condition in your measure.

CumulativeLosses =
CALCULATE (
    SUM ( FactCRTLoanPerformanceMonth[CreditEventNetLossAmount] ),
    FILTER (
        ALL ( FactCRTLoanPerformanceMonth[PerformanceMonth] ),
        FactCRTLoanPerformanceMonth[PerformanceMonth]
            <= MAX ( FactCRTLoanPerformanceMonth[PerformanceMonth] )
    && FactCRTLoanPerformanceMonth[PerformanceYear]
            = MAX ( FactCRTLoanPerformanceMonth[PerformanceYear] )
    )
)

If not your case, please share your sample pbix file and your expected output for use have a test.

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-lili6-msft 

 

Thank you very much for your reply, I have created a sample pbix and used ALL function and IT WORKED! 

 

But the issue is NOT RESOLVED in DirectQuery mode. I use the same exact formula which i used in sample file but somehow things are not working in Direct Query mode. How do I investigate this issue and see what are the factors affecting the result. I will attach my sample file here and also the screenshots from the direct query mode. 

 

Please let me know if you think something is affecting the result in the direct query mode.

 

Thank you very much for your time.

 

VidyaDirect Query modeDirect Query modeSample FileDirect Query modeDirect Query mode

hi, @Anonymous 

The file doesn’t exist, please share it again.

Do you mean that the same formula in the same data model, when it is import it works and when it is directquery it doesn't work?

there are some limitations in dax function when using directquery, what is your formula?

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

https://www.dropbox.com/s/4acsbhbmko1wh1m/Sample%20for%20PowerBICommunity.pbix?dl=0

 

Please find the sample file.

 

Formula used in sample file:

CALCULATE(SUM('Import for sample Market Risk'[CreditEventNetLossAmount]),FILTER(ALL('Import for sample Market Risk'[DevelopmentAnnualDisplay]),ISONORAFTER([DevelopmentAnnualDisplay],Max([DevelopmentAnnualDisplay]
 
Formula used in DirectQuery :
CALCULATE(SUM(TableName[CreditEventNetLossAmount]),FILTER(ALL(TableName[DevelopmentAnnualDisplay]),ISONORAFTER(TableName[DevelopmentAnnualDisplay]
 
Thank for the links on directquery limitations, i will go through that.

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:
2.JPG
 

Best Regards,

Lin

 
 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.