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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kohrinn
Helper I
Helper I

Pie chart + measure?

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?

 

Kohrinn_0-1693221072842.png

 

Thanks,

Joanna

 

1 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 😉

View solution in original post

8 REPLIES 8
Sergii24
Super User
Super User

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

Sergii24_0-1693231529395.png

 

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

Sergii24_1-1693232754494.png

 

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 🙂

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. ☹️

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 😉

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 ☹️

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! 

Thank you! 😄

Sergii24
Super User
Super User

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors