Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative total from Matrix column

Dear Guru,   I do struggle with cumulative total of two measures. My orderbook is in day, forecast in week.  I calculate Order book per week. I calculate Forecast per week. For each week, Demand ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hello ! 

     

    Thanks a lot for feedback ! Finally I did came out talking to some contact I have found internally that is not far from yours, except that I cannot addcolumn due to differente dimensions in between tables. 

     

    One of the issue is that my data table is sorted by day. Orders are daily, while forecast is weekly conentrated on one day. Then suming cumulative looking at day bucket Demand = Max ORder/Forecast, was not correct. I did add a column to put all orders to last day of the week, same day than Forecast, and use this one as relation with date table. My orders become weekly, on the same day than forecast.

     

    On top, using as you advice SUMX and not CALCULATE was the solution. Below works finally like a charm !

     

    Demand_Cumulative =
    SUMX (
    FILTER (
    ALLSELECTED ( DateTable[Date] ),
    DateTable[Date] <= MAX ( DateTable[Date] )
    ),
    [Demand]
    )
     
    Hope can helps others on this forum !
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

I am not sure how your dim DateTable looks like, but please try something like below.

 

Demand_Cumulative =
SUMX (
    FILTER (
        ADDCOLUMNS ( ALL ( 'DateTable'[yearweeknumbercolumn] ), "@demand", [Demand] ),
        'DateTable'[yearweeknumbercolumn] <= MAX ( 'DateTable'[yearweeknumbercolumn] )
    ),
    [@demand]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello !

     

    First thanks a lot for answering me, at least I feel less alone in front of my issue !

     

    My date table is a table with all dates, and I got many columns to get week number, fiscal period, in the past etc... So I got a column WeeknYear that can match your yearweeknumbercolumn.

     

    Demand_Cumulative =

    SUMX (
    FILTER (
    ADDCOLUMNS ( ALL ( 'DateTable'[WeeknYear] ), "@demand", [Demand] ),
    'DateTable'[WeeknYear] <= MAX ( 'DateTable'[WeeknYear] )
    ),
    [@demand]
    )

    Unfortunately my exemple is for one sku one country. In my data base I got thousnads of SKU for dozaine of countries, the formula is running very long time and results is even worse : I assume it is because it cannot creates thousands of columns in the datetable for each week to create the demand per each SKU and country.

     

    Result I got is for each week, demand cumulative is only the max of each week between order and forecast (not suming cumulative at all)

     

    Current Week1234567
    Forecast5105515105
    Order Book303200200
    Demand cumulative wrong30105515205
    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      I am not sure but I assume your 'DateTable'[WeeknYear] column is sorted by other column. In this case, the ALL function needs to be applied to the sort column as well. 

      Please share your sample pbix file's link here, and then I can try to look into it to come up with a more accurate solution.

      Thanks.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello ! 

         

        Thanks a lot for feedback ! Finally I did came out talking to some contact I have found internally that is not far from yours, except that I cannot addcolumn due to differente dimensions in between tables. 

         

        One of the issue is that my data table is sorted by day. Orders are daily, while forecast is weekly conentrated on one day. Then suming cumulative looking at day bucket Demand = Max ORder/Forecast, was not correct. I did add a column to put all orders to last day of the week, same day than Forecast, and use this one as relation with date table. My orders become weekly, on the same day than forecast.

         

        On top, using as you advice SUMX and not CALCULATE was the solution. Below works finally like a charm !

         

        Demand_Cumulative =
        SUMX (
        FILTER (
        ALLSELECTED ( DateTable[Date] ),
        DateTable[Date] <= MAX ( DateTable[Date] )
        ),
        [Demand]
        )
         
        Hope can helps others on this forum !