Forum Discussion

t_patts's avatar
t_patts
Frequent Visitor
4 years ago
Solved

Modeling Changes to Pi Chart Over Time

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?

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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:

     

  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

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

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      t_patts
      Frequent Visitor
      4 years ago

      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's avatar
        AlexisOlson
        Super User
        4 years ago

        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] ) )
)
  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    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