Pie chart + measure?

[Kohrinn]

Hi,

I have a table, where I keep the start and end dates of Events and SubEvents. I assume, that Event starts when first SubEvent starts and ends when the last SubEvent ends.

There are gaps between SubEvents - they are not starting immediatelly one after another. 

Now I want to make a pie chart of percentage duration of each SubEvent to total duration of Event. The chart should include idle time as separete category.

How to tackle with this one?

Thanks,

Joanna

[Sergii24]

There are 2 important steps:

1. Add "Idle" as a subevent for each event (in the worst case its values will be 0)
2. Create a dedicated "Duration" calculation for newly created "Idle" row

First we need to obtain this table in Power Query

Then you can use calculated column for the calculation of "idle" duration:

First we need to obtain total event time:

Duration of Event = 
VAR _CurrentEvent = SELECTEDVALUE( Table_Events[Event] )
VAR _StartOfSelectedEvent = 
    MINX(
        FILTER(
            Table_Events,
            Table_Events[Event] = _CurrentEvent
        ),
        Table_Events[Start]
    )

VAR _EndOfSelectedEvent = 
    MAXX(
        FILTER(
            Table_Events,
            Table_Events[Event] = _CurrentEvent
        ),
        Table_Events[End]
    )

RETURN _EndOfSelectedEvent - _StartOfSelectedEvent

And then we can calculate duration of each of subevents:

Subevent Duration = 
VAR _CurrentEvent = SELECTEDVALUE( Table_Events[Event] )
RETURN
    IF(
        Table_Events[Subevent] <> "Idle",
        Table_Events[End] - Table_Events[Start],
        Table_Events[Duration of Event] - 
            SUMX(
                FILTER(
                    Table_Events,
                    Table_Events[Event] = _CurrentEvent
                ),
                Table_Events[End] - Table_Events[Start]
            )
    )

I've uploaded a sample *.pbix file here. I'll keep it here for you for the next 20-30 days 🙂

[Kohrinn]

It looks really promising - one question though. Is it any way around adding "Idle" subevent to every event? I have like hundreds of Events now and they are still coming so adding manually "Idle" is not a solution. ☹️

[Sergii24 · Accepted solution]

Take a closer look at *.pbix file. "Idle" subevent is automatically added to each event in PowerQuery, so you don't need to it manually 😉

[Kohrinn]

I tried it and I can't find the piece of code where "Idle" is added to each event automatically 😓

I've copied your caclulations and nothing happened ☹️

[Sergii24]

Hi @Kohrinn, before copy-paste code from PowerQuery try to better understand it. I haven't seen your data, so you should first understand the proposed logic in that example, then compare the initial table with your and perfrom modificaitons step-by-step. To help you further I would need more details on what you've tried, what do you struggle with and sample of your raw data. Let me know if any help is needed! 

[Kohrinn]

Thank you! 😄

[Sergii24]

Hi @Kohrinn, you're on a right way! Try to replicate exactly same structure in PowerBI. Add "Duration" as a column of your dataset and then create a pie chart using "Event" and "Subevent" as legend items and "Duration" as values and format values as percentage. Let us know if you face any other issues (better if you can share your pbix in that case 😉 )

[Kohrinn]

What about this "Time Idle" that I have to calculate and then somehow merge with the rest of SubEvents? This part is what I'm struggling with