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
Super 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 !