3

I have data showing multiple people's records on multiple days. Each row also shows the week of the year that said day happened on. Some example data:

Date Week of year Person Commission
2020-12-20 51 Alice $3
2021-12-20 51 Alice $4
2020-12-20 51 Bob $14
2021-12-20 51 Bob $22
2020-12-31 52 Alice $34
2021-12-31 52 Alice $42
2020-12-31 52 Bob $4
2021-12-31 52 Bob $2

What I want is to plot a line graph that shows 'Week of year' on the x-axis, but actually plots one value - the average commission between the two employees - for each day in each week per year. Is this possible?

Whenever I tell Power BI to use 'Week of year' in the x-axis and the year part of 'Date' as the legend, it gives the correct x-axis and correctly gives me one line per year. However, it clearly uses the average value of 'Commission' for each week rather than using each day's value. That is, it gives me about 52 values on the line when I really want about 365. Using 'Date' as the x-axis appears to give me the correct lines, but then I don't have the x-axis that I want.

If it helps, I already have a table that converts each date to its corresponding week.

J. Mini
  • 1,868
  • 1
  • 9
  • 38
  • You could create an extra column in you date table with weeknumber and shortday? (So 51 mon, 51 tue, 51 wed etc.) That way you dont have the full date on the axis but you have axis to each day. The axis will get cluttered however, which is a downside. But I think there is currently no other way to achieve what you want, I have seen it being suggested to Power BI however. – Max Dec 31 '21 at 10:09

1 Answers1

1

I'm not a PowerBI user, but if you can use it to plot from two tables, and the first table is in Excel, make a second table that has the average for each day in the first table. Copy all the dates to a new column, remove the duplicates and then calculate the average commission using a formula like

=AVERAGEIFS(salesCom[Commission],salesCom[Date],"=" &H3)

Make the result into your second table and add to PowerBI.

user3469318
  • 11
  • 4