Modeling Changes to Pi Chart Over Time

[t_patts]

How can I model the changes to a Pi chart over time?

Some context: I have a report for the amount of items that are late broken out by their level of lateness in Pi chart form.

My client has asked I show the changes to this report from week to week.

What would be the best way to do this? Line and stacked column chart?

Will I need to have seperate excel documents/sheets for each week and pull all of them in?

Can I have Power BI track the deltas?

[AlexisOlson · Accepted solution]

A stacked area or stacked bar chart are decent options to visualize this sort of thing.

Here are examples of what they might look like:

[v-easonf-msft]

Hi, @t_patts 

Power BI can't save historical data and track deltas.

You need to manually pull the weekly data for comparison and you can consider using the folder connector to access these pre-prepared data.

As mentioned by @AlexisOlson ,area charts can be good choice to visualize this.

Best Regards,
Community Support Team _ Eason

[AlexisOlson · Accepted solution]

A stacked area or stacked bar chart are decent options to visualize this sort of thing.

Here are examples of what they might look like:

[t_patts]

https://drive.google.com/drive/folders/16oFJXo9iPINyP2KlOJlgcVRfXoxmi8MX?usp=sharing

^^^ Here is my scrubbed data. I have Power BI "Get Data" ing by pulling in the folder and then summarizing the top 10 descending rows (so data is added automatically when a new data file is added).

Here is what it looks like:

Here are my current parameters for the line and stacked column chart:

 

The proportion by week is renamed but its the % GT of count for equipment description of the aggregate files.

I know the selection method (summarize and combine) is the reason the total weekly percent is not 100, its X% of all of the items in all of the files being combined and summarized.. I would like to have it look like your second graph where each week has 100% of each weeks items (seperate from other weeks), not X% of total items from both weeks spreadsheet.

Can I do this while still pulling in new files each week automatically or will I have to manually pull the files to keep the items seperate? What can I do to get each week as its own set?

[AlexisOlson · Accepted solution]

Yes, this is possible. You just need to write a measure to take the proportion over the appropriate granularity. For example, the measure for your bars might be

Proportion =
DIVIDE (
    COUNTROWS ( Table1 ),
    CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1[Late Level] ) )
)

and the line measure

Late% =
DIVIDE (
    CALCULATE ( COUNTROWS ( Table1 ), Table1[Late Level] <> "On Time" ),
    CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1[Late Level] ) )
)