Use a Slicer to show only part of a visual (exclude one column from a filtered visual)

[LucySinclair]

Hi Everyone, 

I am fairly new to PowerBI and wondering if the following is possible:

I would like to use a slicer to show client actuals and forecasts - however in the visual I also show the budget for each month (this is not broken down by client.

Basically when I filter by client (e.g. ABC) I would like the visual to only show the actual and forecast and not show the budget column as it currently does. (As the total budget is much higher than an individual client's forecast/actual and so you can't see the data labels well when filtered)

Currently looks like this:

but would like the filtered view to look like this:

While the unfiltered view should still show the budget column too.

I hope this makes sense and thank you for your time!

[v-yangliu-msft · Accepted solution]

Thanks for the reply from @amitchandak , please allow me to provide another insight:

Hi  @LucySinclair ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
IF(
    NOT(ISFILTERED('Table'[client])),
    SUM('Table'[Value]),
    SUMX(
        FILTER('Table',
       'Table'[Group] <> "Budget"),[Value]))

2. Result:

The slicer has no selected state:

Slicer selected state:

If the results don't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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

[LucySinclair]

Hi @amitchandak , thank you so much for your reply! 

I am having some success with your formula but for some reason it is showing the opposite of what I need - so when my graph is unfiltered the budget shows as 0 and when I filter for a client, then the budget column shows. 

My formula is as follows: 

NewBudget = if(isfiltered('Source Data FY24'[Client]), sum('totals table'[Budget FY24]), blank())

I am not sure if there is a way to reverse this - thank you very much for taking the time to reply and your youtube channel looks very helpful too 🙂

[amitchandak]

@LucySinclair , Create you budget measure like

New budget measure on current budget measure

Budget new =

if( isfiltered(Client[Client]), [Budget], blank())

HasOneValue , Isfiltered: https://www.youtube.com/watch?v=hXg3kRFSGjA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=38
https://youtu.be/cN8AO3_vmlY?t=10120