Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

5 REPLIES 5
DataZoe
Employee
Employee

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

PaulDBrown
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.