cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KWelsh8144 Regular Visitor
Regular Visitor

Average on same graph as actual count per facility

Untitled.png

 

Facility Comparison.PNG

 

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?

3 REPLIES 3
Moderator v-qiuyu-msft
Moderator

Re: Average on same graph as actual count per facility

Hi @KWelsh8144

 

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. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
KWelsh8144 Regular Visitor
Regular Visitor

Re: Average on same graph as actual count per facility

@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?

Highlighted
Moderator v-qiuyu-msft
Moderator

Re: Average on same graph as actual count per facility

Hi @KWelsh8144

 

You need to set the month values (2017 Nov, 2017 Dec,..,etc) in one column field. Assume the raw data looks like below: 

 

q1.PNG

 

You can select the columns 2019-1 and 2019-2 then click on Unpivot Columns, 

 

q2.PNG

 

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. 

 

q3.PNGq4.PNG

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.