Re: Cumulative Total

JPY · ‎11-26-2019

Hi All,

I want to come up with a graph that shows the cumulative orders for years that has orders. For example,there were 2 orders in 1996 , 0 in 1997 and 1 order in 1998. So ideally, on a graph, for the year 1998, there would be 3 orders(inclusive of the 2 orders in 1996). I found this formula from the forums that suggested that this would allow me to get the cumulative number, however, it doesnt seem to be working since i don't get the correct number.

This is the formula that I used:

Target = CALCULATE(COUNT('IB_SEAK HKM_HW'[PCSN]),Filter(ALL('IB_SEAK HKM_HW'),'IB_SEAK HKM_HW'[Date Installed].[Date]

<=MAX('IB_SEAK HKM_HW'[Date Installed].[Date])))

This is what I get:

The table on the right is an accurate summary of the data. As you can see, the graph incorrectly shows that there are 3 orders for the year 1997 when there are actually no orders at all. There shouldnt have year 1997 in the graph. This inaccuracy is reflected for all the years displayed in the graph.

Does anyone know what's wrong with the formula? Or why it has gone awry?

Thanks very much

MartynRamsden · ‎11-27-2019

Hi @JPY

Try this:

Target = 
CALCULATE (
    COUNT ( 'IB_SEAK HKM_HW'[PCSN] ),
    FILTER (
        ALL ( 'IB_SEAK HKM_HW' ),
        'IB_SEAK HKM_HW'[Date Installed] <= MAX ( 'IB_SEAK HKM_HW'[Date Installed])
    )
)

Best regards,

Martyn

JPY · ‎11-27-2019

Hi @MartynRamsden,

I tried this and i still don't get the accurate result.

This is what I got:

If you refer to the table that I provided in the original post, you'd see that the number is correct for both 1996 and 1997 but is inaccurate for the rest of the years. For example, for 1998, the number should be 2+0+ 1 = 3 but the measure that you suggested renders 5 instead.

Thanks for the suggestion though! Would you happen to know why the rest of the years are wrong?

MartynRamsden · ‎11-27-2019

Hi @JPY

I think I understand why that didn't work - are you using a date dimension table?

If so, try this:

Target = 
VAR MaxDate = MAX ( 'Calendar'[Date] )
VAR Result = 
CALCULATE (
    COUNTROWS( 'IB_SEAK HKM_HW' ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MaxDate)
    )

RETURN 
Result

Best regards,

Martyn

JPY · ‎11-27-2019

Hi @MartynRamsden,

Thanks for the reply!!

I think I found the problem. I should use ALLEXCEPT('IB_SEAK HKM_HW','IB_SEAK HKM_HW'[Model]) instead of "ALL" since I have a filter applied on Model.

Thank you!

Joyce

MartynRamsden · ‎11-28-2019

Excellent - glad you figured it out!

JPY · ‎11-28-2019

Hi @MartynRamsden,

Thanks for providing the previous formula! It helped me a great deal.

However, I seem to have encountered another obstacle.

I used the formula that is listed below and when i look at the data on a yearly basis, the graphs that I have for the year and the model are accurate but the graph showing the cumulative number of models by year and sub-region is wrong.

For example, in 2000, there were 8 machines and this is reflected correctly in the diagram showing the total number and the model since there are 5 HE and 3 LE (thus, 8 machines). However, this isnt reflected in the diagram which shows the number of machines by year and sub region, since the total number shown is only 5 (SEA NORTH) + 1 (SEA SOUTH) = 6.

CumulativeTotal = CALCULATE(SUM('IB_SEAK HKM_HW'[Index]),FILTER(ALLEXCEPT('IB_SEAK HKM_HW','IB_SEAK HKM_HW'[Sub-region],'IB_SEAK HKM_HW'[Model],'IB_SEAK HKM_HW'[Account: Country]),'IB_SEAK HKM_HW'[Date Installed]<=MAX('IB_SEAK HKM_HW'[Date Installed])))

2

This table above verifies that I do have 3 machines in SEA South and 5 machines in SEA North. '

1

Do you know why this is happening? Is there any part of the measure that I am using that causes this problem? I've also applied some model filters and this can be seen on the extreme right hand side of the above screenshot.

Thank you for your help!

Best regards,

JPY