Forum Discussion
Subtotal YTD in Matrix
Hello,
I've created a YTD measure in a matrix and I'd like to show a subtotal summing years by category. The problem I'm facing is that the subtotal shows just the last year and does not aggregate the years.
Anyone has ideas about how to fix it?
Thanks in advance,
Angelo
The TOTALYTD function works in the year context only. Thus I created a custom mesaure has suggested in this post:
https://community.powerbi.com/t5/Desktop/Cumulative-Running-Totals-in-DAX/m-p/42608
Cumulative Value =
IF(
MIN(Dim_Calendar[Date])<=CALCULATE(
MAX(Dim_Calendar[Date]);
ALL(TF));
CALCULATE([Valore_M];
FILTER(All(Dim_Calendar[Month]);Dim_Calendar[Month]<=MAX((Dim_Calendar[Month]
))));BLANK())Now it works!
3 Replies
Hi angelocecinati,
Please try this:
ValueYTD = SUMX ( sales_table, TOTALYTD ( [Value_M], 'Dim_Calendar'[Date] ) )
Best regards,
Yuliana Gu
Hi Yuliana,
Thanks for your reply. This solution makes the subtotal works properly. Unfortunatly the YTD measure does not work over the months, cause it should sum the passed period values. .
I think that if I put the sumx function in front of the measure it computes the YTD for the single row of the fact table.
If I change the order of the matrix it works!
Do I have to change the model behind?
Thanks again,
Angelo
The TOTALYTD function works in the year context only. Thus I created a custom mesaure has suggested in this post:
https://community.powerbi.com/t5/Desktop/Cumulative-Running-Totals-in-DAX/m-p/42608
Cumulative Value =
IF(
MIN(Dim_Calendar[Date])<=CALCULATE(
MAX(Dim_Calendar[Date]);
ALL(TF));
CALCULATE([Valore_M];
FILTER(All(Dim_Calendar[Month]);Dim_Calendar[Month]<=MAX((Dim_Calendar[Month]
))));BLANK())Now it works!
