Find average of measure

[Tevon713]

I need help finding averages of a measure per region/time period. Given the user able to select time period and region, I want to display in the chart two averages along with the measure; 1) Average of Select Region(s) 2) Static Average of all the Region which doesn't change.

How would I go about calculating and displaying it? Thank you all.

Link to the sample pbix

https://www.dropbox.com/s/qc2okhdd5wt6ee2/Find%20average%20of%20measure.pbix?dl=0

Table Fields

Measure

CURRENT VISUAL

[selimovd · Accepted solution]

Hey @Tevon713 ,

I hope I understood it right, here the measure for the average per region:

Average of Select Region =
IF (
    [Total Visit per Staff] <> BLANK (),
    CALCULATE ( [Total Visit per Staff], ALLEXCEPT ( SiteName, SiteName[Region] ) )
)

And here the static one:

Static Average of all the Region = 
IF (
    [Total Visit per Staff] <> BLANK (),
    CALCULATE ( [Total Visit per Staff], ALL ( SiteName ) )
)

Be aware that in your report you are using the fields from the fact table. You should avoid that and only use the fields from the dimensional tables as they should be used to filter.

Here the result:

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[Tevon713]

Thank you @selimovd for your help. Lesson learned on using fields from fact table.

Got one more question, this regarding the chart display... how to display only single bar for the avg of select region and static avg of all the region? Is that even possible with it being a measure? Trying to declutter it.

[selimovd]

Hey @Tevon713 ,

as far as I know that's not possible with the default visuals. 

If you use a bar chart they will all be bars. Only you could do these measures as lines with the clustered column charts and lines:

But if you have special requirements, maybe take a look into Charticulator (https://charticulator.com/).

With Charticulator you can create your own visuals with moderate effort (compared to program an own visual). Check YouTube for a few tutorials how to do that.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic