CODEWITHSUNDEEP
graphpowerbilinegraph
J. Mini
J. Mini

Reputation: 1610

Can the line on a line graph use an input that is linked to the x-values shown on the x-axis?

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.

Upvotes: 3

Views: 136

Answers (1)

user3469318
user3469318

Reputation: 11

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.

Upvotes: 1

Related Questions