Venn Diagram pieces with slicers

[Thigs]

Hi! I have data trying to see if certain SKUs are sold in our retail space or online space. I am trying to calculate the number of skus sold online, sold in retail, sold in both, sold only online, and sold only in retail (basically the pieces of a Venn Diagram). 

I am able to get all the calculations correctly - HOWEVER - when I use a slicer to then select retail or online, the calculations become incorrect. 

Formulas so far - 

TotalSKUsUnfiltered = CALCULATE(DISTINCTCOUNT('Data'[SKU Code]), ALL('Data'[Channel]))

RetailSKUs = CALCULATE(

    DISTINCTCOUNT('Data'[SKU Code]),

    FILTER(ALL('Data'[Channel]), 'Data'[Channel] = "Retail"))

RetailOnlySKUs = TotalSKUsUnfiltered-RetailSKUs

However, when I select "Retail", the number of SKUs increases from what it should be. Any assistance would be greatly appreciated!

[Zanqueta · Accepted solution]

Hi @Thigs  INTERSECT is indeed an elegant and robust way to construct the different parts of your Venn diagram (Retail, Online, Both, Retail only, Online only). This approach works directly with sets of SKUs, which helps avoid many of the context issues you are encountering when slicers are applied.

The key idea is to:
Build a set (table) of SKUs that are sold in Retail.
Build a set of SKUs that are sold Online.
Use INTERSECT to find SKUs present in both sets.
Use EXCEPT to find SKUs that are only in one channel.
All of this should be done in a way that respects your report filters and slicers.

1. Define the SKU sets per channel

These expressions return a table of distinct SKUs for each channel, while preserving the current filter context (including other slicers such as date, category, etc.):
 
RetailSet :=
CALCULATETABLE(
VALUES ( 'Data'[SKU Code] ),
KEEPFILTERS ( 'Data'[Channel] = "Retail" )
)

OnlineSet :=
CALCULATETABLE(
VALUES ( 'Data'[SKU Code] ),
KEEPFILTERS ( 'Data'[Channel] = "Online" )
)
 
2. SKUs sold in both channels (intersection)
BothSKUs :=
COUNTROWS (
INTERSECT ( RetailSet, OnlineSet )
)
 
3. SKUs sold only in Retail
RetailOnlySKUs :=
COUNTROWS (
EXCEPT ( RetailSet, OnlineSet )
)
 

4. SKUs sold only Online
OnlineOnlySKUs :=
COUNTROWS (
EXCEPT ( OnlineSet, RetailSet )
)
5. Total SKUs (if needed)
If you also need a simple total that respects the current filters:
TotalSKUs :=
DISTINCTCOUNT ( 'Data'[SKU Code] )
 

[Thigs]

This was very helpful, thank you! I had never used intersect before, that is exactly what I needed!

My one thought - currently, the "Both" option goes blank when I select one or the other options. Could I have it remain at the current value if I select one or the other option?

[v-kpoloju-msft]

Hi @Thigs, 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @Zanqueta,  for his inputs on this thread.

Great to hear that the INTERSECT approach helped resolve the original issue thanks for confirming.

Regarding your follow-up question, the “Both” values going blank when selecting either Retail or Online is expected behaviour. When the slicer filters the data to a single channel, the dataset no longer contains both channels at the same time, so the INTERSECT result becomes empty.

If you would prefer the “Both” values to remain visible even when one channel is selected, you could adjust the measure, so the calculation ignores the slicer filter on the Channel column while still respecting other report filters. This can be done using functions such as REMOVEFILTERS('Data'[Channel]) or ALL('Data'[Channel]) within the measure.

This way, the Both calculations can still evaluate Retail and Online together, regardless of the slicer selection.

Thank you for using the Microsoft Fabric Community Forum.