How do I add an include/exclude slicer that retains some of a rows data??

[Faw]

Hi,

I have a dashboard pulling data from a .csv file with the data columns below (the pic below is an abridged excel version with dummy data).

I'd like to add is a slicer referencing the 'Campaign' column that allows me to 'Include ad-hoc campaign value' or 'exclude ad-choc campaign value' but (and this is where I've got lost online) I only want it to include or exclude the ad-hoc campaign value from the calculated 'Total Value' column - I want to retain the sum of sales in my dashboard (and just filter in/out the resulting value of these ad-hoc sales)

So based on my snippet below 'Include ad-hoc campaign value' would return 960 sales with a value of £19,300.

'Exclude ad-hoc campaign value' would return 960 sales with a value of £12,000.

Any help would be hugely appreciated - Thanks!

[parry2k · Accepted solution]

@Faw you need to create another table with two options, include or exclude and then use it in the slicer, and for total value use this measure:

Total Value = 
SUMX ( 
    YourTable,
    VAR __selectedSlicerValue = SELECTEDVALUE ( SlicerTable[SlicerCol], "Include" )
    VAR __ProductValue = IF ( __selectedSlicerValue = "Exclude" && YourTable[Campaign] = "Ad-hoc", 0, YourTable[Product Value] )
    RETURN
       YourTable[Qty] & __ProductValue
)
 

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

[Faw]

Thanks so much - thats now working and its a great bit of knowledge for future projects!

[parry2k]

@Faw does it need to be a column? As a best practice, you should be adding this as a measure.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

[Faw]

Hi @parry2k , it's currently a column, but I can change it to a measure (especially if it leads to a solution for this!) Thanks