cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giuco_82
New Member

Create a Table with Custom Cumulative total

Hi,

 

I have a RAW Table like this:

RAW_Table

CodeCurrent_Phase
AA1Phase 1
AA2Phase 3
AA3Phase 2
AA4Phase 5
BB1Phase 6
BB2Phase 6
CC1Phase 4
CC2Phase 1
CC3Phase 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:

PhaseTotal
Phase 19
Phase 27
Phase 36
Phase 44
Phase 53
Phase 62

 

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!

1 ACCEPTED SOLUTION
DataZoe
Microsoft
Microsoft

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

DataZoe_0-1660605642727.png

 

Join on Current Phase (M-M relationship)

 

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

 

DataZoe_1-1660605750743.png

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

5 REPLIES 5
DataZoe
Microsoft
Microsoft

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

DataZoe_0-1660605642727.png

 

Join on Current Phase (M-M relationship)

 

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

 

DataZoe_1-1660605750743.png

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

PaulDBrown
Super User
Super User

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] )
    )
)

result.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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!

Hi @PaulDBrown ,

 

I got all the cumulative phases to 2:

 

giuco_82_0-1660607765634.png

 

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

result 1.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors