Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a dataset that looks like this:
Account/Donor | Amount | Source | Stage | Donor Decision Date |
Donor | $1,299,998 | HQ | Awarded | Monday, October 5, 2020 |
Foundation | $75,905 | HQ | Awarded | Thursday, October 1, 2020 |
Person A | $4,032,428 | HQ | Awarded | Wednesday, October 21, 2020 |
Donor | $12,598 | HQ | Awarded | Tuesday, October 13, 2020 |
Donor | $50,000,000 | HQ | Rejected | Friday, October 2, 2020 |
Donor | $2,999,890 | HQ | Awarded | Friday, October 16, 2020 |
Person A | $632,119 | HQ | Awarded | Wednesday, October 21, 2020 |
Foundation | $33,906 | Field | Awarded | Thursday, 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:
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!
Solved! Go to Solution.
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" )
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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |