Problems with cumulative total

[scottsinclair]

Hello all

I have 2 tables, for which I want to calculate a cumulative total. I can't seem to get it to "cumulate" over the entire period; it want's to reset the figure for each month.

For the purpose of ease, table 1 has 2 columns; Month, and Output (m). Table 2 has Month, and reject (m). I want to divide one figure by the other (a la a percentage rejects), which I will then display on a graph.

I have created a third table (Table 3) which has a calendarauto() column, to enabled me to set a relationship to the two fields above. I then used this code in table 2:

DPMO = 
CALCULATE (
    SUM ( Table2[Scrap] )/sum(Table1[Output])*1000000, FILTER(ALL(Table3[Date]),Table3[Date] <= max (Table3[Date] )
    ))

Finally I make a line chart, set Table3[date] as the axis (set to "month"), and...I have unique DPMO values per month, which do not cumulate. What am I doing wrong?

[BIXL]

Hi @scottsinclair,

I think you should calculate each measure with it's own filter criteria.

try this :

MEASURE1=CALCULATE (SUM( Table2[Scrap]),ALL(Table3[Date]),Table3[Date] <= max (Table3[Date]))
MEASURE2=CALCULATE(SUM(Table2[Output])*1000000,ALL(Table3[Date]),Table3[Date] <= max (Table3[Date]))
DPMO=DIVIDE(MEASURE1,MEASURE2)

another thing, you wrote that table 1 and table 2 is on month level, so just to make sure that when connecting to the calendar table you are connecting with the same granularity.

Please click the Accept as Solution if this resolves your issue; if it did not resolve your issue, provide more information or considr uploading a sample of your model.

[scottsinclair]

I understand - how to set granularity for calendarauto() though? Another field / column?

[BIXL]

Hi,

It is best practice to use calendar table with date data type as key.

when you want to connect your other tables to the calendar table, they also need to have a date key to connect with.

If table1 and table2 are on month level but with date data type, there should not be a problem, but if they are in a different datatype (like number or string) you can simply convert them to date using FORMAT or DATEVALUE or add a calculated column using EOMONTH function to get a date data type column and then conect to the calendar table with this calculated column.

[scottsinclair]

Something still wrong. If I plot the measure for cumulative scrap, it should get bigger each month (by definition), but instead I get this:

[BIXL]

can you please share a sample of your model (PBIX file) ?

[scottsinclair]

Uploaded to this link: https://onedrive.live.com/redir?resid=B298D75F6D9FC782!16655&authkey=!AJfXtmM-HKKaBrA&ithint=file%2c...

Stripped out all the superfluous columns for the purpose of this issue.

[BIXL · Accepted solution]

Hi,

I think the problem is when you are using a calculated column. in this case try to use a measure :

M_ACCUMULATED_MetresProduced = CALCULATE(SUM(Table1[Good MetresProduced]),FILTER(ALL(Table3[Date]),Table3[Date]<=MAX(Table3[Date])))

M_ACCUMULATED_Scrap_Qty = CALCULATE(SUM(Table2[Scrap Qty])*1000000,FILTER(ALL(Table3[Date]),Table3[Date]<=MAX(Table3[Date])) )

M_DPMO = DIVIDE([M_ACCUMULATED_Scrap_Qty],[M_ACCUMULATED_MetresProduced])

[scottsinclair]

This is great; it didn't give me the number I wanted, but the principle of displaying a data table alongside has helped me resolve this. I needed to switch the formula to calculate a YTDTOTAL to prevent it summarising figures not in the required dataset. This threw out the overall percentage.

Also added a YYYYMM to the date table to prevent unneccessary summarisation.