How to show "Overall" Average on a bar graph with additional category

[aaron797]

Hi,

How do i achieve the below graph? 

I have a category in the Axis field which seperates the graph into "Sport Coach" and "Specialist Service Provider".

The Value field consists of "Answer" which I then apply an average to.

I would like to show the average on the graph, without splitting it, any idea how to achieve this?

The average line under 'analytics' does not work, as it simply averages the two averages, and i also tried Grouping - however that removes the "Sport Coach" and "Specialist Service Provider".

Any ideas? 

[v-joesh-msft · Accepted solution]

Hi @aaron797 ,

To achieve the result you want, you need to create a calculated table first:

Table 2 = UNION(VALUES('Table'[category]),ROW("c","Overall"))

Then create a measure similar to the following:

Measure =
VAR S =
    SELECTEDVALUE ( 'Table 2'[category] )
RETURN
    IF (
        S = "Overall",
        AVERAGE ( 'Table'[Answer] ),
        CALCULATE ( AVERAGE ( 'Table'[Answer] ), 'Table'[category] = S )
    )

Results are as follows:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EX-WboMvJdZKtfufql...

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

[aaron797]

@v-joesh-msft  Fantastic! Thank you so much. Would you know how to order the values, so Overall is on top or bottom?

[v-joesh-msft]

Hi @aaron797 ,

Select the ellipsis in the upper right corner of the visual--sort by, then select the field you want to sort:

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