I currently have a chart that I produce in Excel that I need to generate in Power BI. The chart shows the averages by month for a certain group of facilities on one line and then the actual number of episodes the facility has on another line, so a facility can compare as to where they stand in comparison the group average. I'm uncertain how/what type of measure to use to make it so that I can show the average by month, by a group of facilities in comparison to the actual count of episodes of one particular facility.
As you can see in the image, I would typically take a Pivot table, and then average the numbers in another row(inserting 0's where there were blanks to get an accurate average if someone didnt have any episodes). On the chart, i would use the line of the actual facility as well as the average line.
How can I do this in PBI?
In Power BI, there is no visual which can display the exact the same result as in Excel. To work around the issue, we can split the Excel chart to two parts and use Line chart visual and matrix visual in Power BI to display it.
From your screenshots, you might need to transform data before creating visuals in Power BI. It would be better if you could share the Excel file with us so we can share detail steps further. If possible, you can upload the Excel to your OndDrive and send the share link to me via private message.
@v-qiuyu-msft I'm actually not able to upload the file and share it outside of my organization through one drive. Do you have any other ideas on how i would need to transform the data?
You need to set the month values (2017 Nov, 2017 Dec,..,etc) in one column field. Assume the raw data looks like below:
You can select the columns 2019-1 and 2019-2 then click on Unpivot Columns,
Then you can go to report page, add a line chart visual, put the Attribute column in Axis bucket and Value to Values bucket. Add a matrix visual, drag the Label column to Rows, Attribute column to Columns bucket and Value to Values bucket.