Create a Table with Custom Cumulative total

[Anonymous]

Hi,

I have a RAW Table like this:

RAW_Table

Code	Current_Phase
AA1	Phase 1
AA2	Phase 3
AA3	Phase 2
AA4	Phase 5
BB1	Phase 6
BB2	Phase 6
CC1	Phase 4
CC2	Phase 1
CC3	Phase 3

Each item identified by "Code" can go from Phase 1 to Phase 6. For example, an item that is at Phase 4, went to Phase 1, Phase 2 and Phase 3 before going to Phase 4. I want to show how many items have been in each phase, based on the Current Phase.

E.g.:

Table to be visualized:

Phase	Total
Phase 1	9
Phase 2	7
Phase 3	6
Phase 4	4
Phase 5	3
Phase 6	2

FYI: I created another table (TableToOrder) for the custom sorter of Phases, to order them as I wanted (as in the reality, they have different names and I don't want to sort them alphabetically).

Thanks!

[DataZoe · Accepted solution]

@Anonymous Another approach is also to create a table with all phrases for the current phrase, and join it. 

Join on Current Phase (M-M relationship)

Then you can just just All Phases and row count from main table.

[PaulDBrown]

See if this works for you:

Cumulative Phases =
CALCULATE (
    COUNTROWS ( 'Raw Table' ),
    FILTER (
        ALL ( 'Raw Table' ),
        'Raw Table'[Current_Phase] >= MAX ( 'Raw Table'[Current_Phase] )
    )
)

[Anonymous]

Hi Paul,

I solved the problem with DataZoe approach. I think my requirements weren't completely clear, so your solution wasn't satisfying my needs. But the DataZoe approach worked. Thanks!

[Anonymous]

Hi @PaulDBrown ,

I got all the cumulative phases to 2:

[PaulDBrown]

What measure are you using? this is what I get: