Forum Discussion

angelocecinati's avatar
angelocecinati
Frequent Visitor
8 years ago
Solved

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 aggregat...
Need Help
Tips and Tricks
  • angelocecinati's avatar
    angelocecinati
    8 years ago

    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!

     

     

     

     

     

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi angelocecinati,

 

Please try this:

ValueYTD =
SUMX ( sales_table, TOTALYTD ( [Value_M], 'Dim_Calendar'[Date] ) )

 

Best regards,

Yuliana Gu

  • angelocecinati's avatar
    angelocecinati
    Frequent Visitor
    8 years ago

    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

     

  • angelocecinati's avatar
    angelocecinati
    Frequent Visitor
    8 years ago

    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!