Slicer that does not affect a value on X-axis for comparisons

[Aimeeclaird]

Hi, I want to create a column chart visual that allows users to compare the overall to specifics.

For example, I am working with school data and want to show the national average in the first column, then the individual school averages in the other columns. I want the user to be able to select the school from a slicer, but the national average to always remain the same.

Apologies, given the nature of the data I am using I am unable to provide an accurate sample. A 'mock up' can be found here - PBI Community Example - 280921.xlsx

[v-eqin-msft]

Hi @Aimeeclaird ,

Yes, please try:

New table =
ADDCOLUMNS (
    UNION ( { "National Average", "Public Average" }, VALUES ( 'Table'[School] ) ),
    "Average",
        SWITCH (
            [Value],
            "National Average", AVERAGE ( 'Table'[Average] ),
            "Public Average", 0.98,
            CALCULATE (
                AVERAGE ( 'Table'[Average] ),
                FILTER ( 'Table', [School] = [Value] )
            )
        )
)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Aimeeclaird]

@v-eqin-msft Thank you so much for your help. This is perfect!

[v-eqin-msft · Accepted solution]

Hi @Aimeeclaird ,

@parry2k ‘s post is in detail. Only as a supplement, I created a new table similar with his blog:

New table =
ADDCOLUMNS (
    UNION ( { "National Average" }, VALUES ( 'Table'[School] ) ),
    "Average",
        IF (
            [Value] = "National Average",
            AVERAGE ( 'Table'[Average] ),
            CALCULATE (
                AVERAGE ( 'Table'[Average] ),
                FILTER ( 'Table', [School] = [Value] )
            )
        )
)

Here is the final output:

Actually I am not clear about this :Would you be able to advise of a solution that doesn't involve having the actuals as a value. We need to calculate the 'actual' based on all data in the data table. 

Please explain more or provide us with your expected output.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Aimeeclaird]

Hi @v-eqin-msft Thanks. This is really helpful.

Another follow up question. Is it possible to adapt the code you've provided to include another value? It isn't a calculation, it would just be a known number I want to add in as the 'Public Average' = 0.98 

[parry2k]

@Aimeeclaird check one of my blog posts talks about a similar topic, you can tweak the solution to meet your needs. Compare Budgeted Scenarios vs. Actuals | PeryTUS IT Solutions

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Aimeeclaird]

Hi @parry2k Would you be able to advise of a solution that doesn't involve having the actuals as a value. We need to calculate the 'actual' based on all data in the data table. 

[Aimeeclaird]

Thanks for the quick help @parry2k At a glance this looks like the solution, will have a proper go following your post.