Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Slicer not filtering Cumulative Total

Hi, 

 

I have a dataset that looks like this:

Account/DonorAmountSourceStageDonor Decision Date
Donor$1,299,998HQAwardedMonday, October 5, 2020
Foundation$75,905HQAwardedThursday, October 1, 2020
Person A$4,032,428HQAwardedWednesday, October 21, 2020
Donor$12,598HQAwardedTuesday, October 13, 2020
Donor$50,000,000HQRejectedFriday, October 2, 2020
Donor$2,999,890HQAwardedFriday, October 16, 2020
Person A$632,119HQAwardedWednesday, October 21, 2020
Foundation$33,906FieldAwardedThursday, October 15, 2020

 

I have a measure that returns the sum of amount that's been awarded:

 

 

 

FY21_Awarded = CALCULATE(
    sum(FY21[Amount]), FILTER(FY21, FY21[Stage] = "Awarded"))

 

 


I have another measure that returns the cumulative total of the amount awarded:

 

 

 

FY21_Cumulative Total = CALCULATE(
    FY21[FY21_Awarded], 
    FILTER(ALL(FY21), FY21[Donor Decision Date] <= MAX(FY21[Donor Decision Date])))

 

 

 

This all works great as anticipated:

 

 However, as soon I user the source slicer I get the following:

 

You'll see that the total returns $34k for the one record. However, the cumulative total is returning $1.42M. I understand that this is working as the measure intended and that on the date of the slicer selection of "Field" the cumulative total including the $34k is equal to $1.42M.

However, I would like the slicer to filter the cumulative total measure:

  • If nothing is selected it would return the cumulative total for field and HQ. 
  • If field or HQ is selected it would only return the cumulative total of awarded for either Field or HQ. 

I'm essentially looking for a dynamic cumulative measure based on the field and HQ slicer. 

 

Any information on how to proceed would be greatly appreciated.

 

thanks in advance!

 

 

  • Anonymous 

    Chagne the ALL in your second measure to only affect the [Donor Cecision Date] coulumn:

     

     

    FY21_Cumulative Total =
    CALCULATE (
        [FY21_Awarded],
        FILTER (
            ALL ( FY21[Donor Decision Date] ),
            FY21[Donor Decision Date] <= MAX ( FY21[Donor Decision Date] )
        )
    )

     

    You also don't need the FILTER in your first measure, you can do it like so.

    FY21_Awarded =
    CALCULATE ( SUM ( FY21[Amount] ), FY21[Stage] = "Awarded" )

2 Replies

  • Anonymous 

    Chagne the ALL in your second measure to only affect the [Donor Cecision Date] coulumn:

     

     

    FY21_Cumulative Total =
    CALCULATE (
        [FY21_Awarded],
        FILTER (
            ALL ( FY21[Donor Decision Date] ),
            FY21[Donor Decision Date] <= MAX ( FY21[Donor Decision Date] )
        )
    )

     

    You also don't need the FILTER in your first measure, you can do it like so.

    FY21_Awarded =
    CALCULATE ( SUM ( FY21[Amount] ), FY21[Stage] = "Awarded" )