Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am having trouble getting my totals to calculate correctly for the example below in which a sum filtering by LASTDATE is used. I understand the measure is calculating on the total line, so that's why it's producing an undesired result, but I haven't been able to figure out how to make the total correct.
In the example below, my page has a filter where filter column = Y (so the 4/30/2024 row for item A is excluded). The desire is to show the value from the value column for each item where the date is the largest within each item. So, for A, it should show a value of 3 from the 3/31/2024 record and for B, it should show a value of 4 from the 4/30/2024 record. The totals row should add these two values together for a total of 7. The DAX below is working correctly for each row, but the total is showing 4 (which it is picking up from the 4/30/2024 B record). How can I make this total show 7?
Measure = CALCULATE(SUM('Data'[Value]), LASTDATE('Data'[Date]))
Data:
Item | Value | Filter Column | Date |
A | 1 | Y | 1/31/2024 |
A | 2 | Y | 2/29/2024 |
A | 3 | Y | 3/31/2024 |
A | 3 | N | 4/30/2024 |
B | 1 | Y | 1/31/2024 |
B | 2 | Y | 2/29/2024 |
B | 3 | Y | 3/31/2024 |
B | 4 | Y | 4/30/2024 |
Expected Result:
Item | Measure | Latest Date |
A | 3 | 3/31/2024 |
B | 4 | 4/30/2024 |
Total | 7 | --- |
Current Result:
Item | Measure | Latest Date |
A | 3 | 3/31/2024 |
B | 4 | 4/30/2024 |
Total | 4 | --- |
Solved! Go to Solution.
You may have to use the SUMX function for this.
If you don't have an existing table where your values are equal to your measure, then consider the following:
Measure =
VAR _SumTable =
Summarize(
Data[Item],
"Measure",CALCULATE(Data[Value],LASTDATE(Data[Date])
)
RETURN
SUMX(_SumTable,
[Measure]
)
You may have to use the SUMX function for this.
If you don't have an existing table where your values are equal to your measure, then consider the following:
Measure =
VAR _SumTable =
Summarize(
Data[Item],
"Measure",CALCULATE(Data[Value],LASTDATE(Data[Date])
)
RETURN
SUMX(_SumTable,
[Measure]
)
I stumbled across this after I posted and I think it's basically saying the same thing. I think I have it working using a similar measure.
Measure Totals, The Final Word - Microsoft Fabric Community
Great!
User | Count |
---|---|
78 | |
75 | |
62 | |
60 | |
46 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |