Frequency Line Graph

[jfg28282]

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:

Date	Time	Event
1/1/22	9:00am	Event A
1/1/22	9:10am	Event A
1/1/22	9:20am	Event C
1/1/22	9:30am	Event B
1/1/22	10:00am	Event C
1/2/22	9:00am	Event A
1/2/22	10:00am	Event C
1/3/22	9:10am	Event C
1/3/22	9:20am	Event B
1/3/22	10:00am	Event A
1/4/22	9:00am	Event B
1/4/22	9:30am	Event B
1/5/22	10:00am	Event 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:

Time	Event A	Event B	Event C
9:00am	66%	33%	0%
9:10am	50%	0%	50%
9:20am	0%	50%	50%
9:30am	0%	100&	0%
10:00am	25%	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!

[Shaurya · Accepted solution]

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