Forum Discussion

jfg28282's avatar
jfg28282
Regular Visitor
3 years ago
Solved

Frequency Line Graph

Hi all,

 

I'm very new to power BI and although there were similar questions in the forum already, I couldn't figure out how to apply them to my problem. I'm trying to graph the frequency an event occurs over the course of a day/month. The data I'm working with looks similar to this:

DateTimeEvent
1/1/229:00amEvent A
1/1/229:10amEvent A
1/1/229:20amEvent C
1/1/229:30amEvent B
1/1/2210:00amEvent C
1/2/229:00amEvent A
1/2/2210:00amEvent C

1/3/22

9:10amEvent C

1/3/22

9:20amEvent B

1/3/22

10:00amEvent A

1/4/22

9:00amEvent B

1/4/22

9:30amEvent B

1/5/22

10:00amEvent C

 

Because each day/time of day is unevenly represented, a count of each event is misleading. Below is the graph I'm getting with my full data set:

 

 

 

 

 

 

 

 

 

Each time periods lines should always add up to 100%. For example, in the table above you would have:

TimeEvent AEvent BEvent C
9:00am66%33%0%
9:10am50%0%50%
9:20am0%50%50%
9:30am0%100&0%
10:00am25%0%75%

In excel, I'd use a pivot table and just use the % of row function to show frequency of a particular event. How do I do this in Power BI?

Thank you for any information!

Need Help
  • Shaurya's avatar
    Shaurya
    3 years ago

    Hi jfg28282,

     

    Use this formula to calculate the percentage first:

     

    % = SUMX('Table',IF('Table'[Time]=EARLIER('Table'[Time]) && 'Table'[Event]=EARLIER('Table'[Event]),1,0)) / SUMX('Table',IF('Table'[Time]=EARLIER('Table'[Time]),1,0))

     

    Then add a matrix visual and use time as rows, event as columns and % as values.

     

     

    Works for you? Mark this post as a solution if it does!
    Consider taking a look at my blog: Forecast Period - Previous Forecasts

1 Reply

  • Shaurya's avatar
    Shaurya
    Memorable Member
    3 years ago

    Hi jfg28282,

     

    Use this formula to calculate the percentage first:

     

    % = SUMX('Table',IF('Table'[Time]=EARLIER('Table'[Time]) && 'Table'[Event]=EARLIER('Table'[Event]),1,0)) / SUMX('Table',IF('Table'[Time]=EARLIER('Table'[Time]),1,0))

     

    Then add a matrix visual and use time as rows, event as columns and % as values.

     

     

    Works for you? Mark this post as a solution if it does!
    Consider taking a look at my blog: Forecast Period - Previous Forecasts