Unable to show total on store level when other columns in table is filtered

[dataanalyst123]

Hi,

When figuring out a seemingly simple solution I ran into issues with creating a new subset of the data.

The goal is to work with a new subset

- X means that this column is filtered in the sidebar/filter pane. These filters are mostly static but are able to be changed in the future.

Store Name	Konzern	Sales Org ID	Ops Sub Channel Name	Ops Sub Channel	Store Cluster	Sales Qty
a	2					1000
b	2					4000
c	2	X				1500
d	3	X	X			3000
e	3	X	X	X		1500
f	3	X	X	X	X	1000
g	4	X	X	X	X	4000
h	5	X	X	X	X	1500
i	5	X	X	X	X	3000
j	5	X	X	X	X	500
						Total 21000

The goal of the measure was to do the following.

Store name	Konzern	Sales qty	Sales Qty all customers
f	3	1000	10000
g	4	4000	10000
h	5	1500	10000
i	5	3000	10000
j	5	500	10000

Sales Qty all customers should then only sum the values for the new subset

(so only the stores that remain after filtering -> See stores 6-10)

So far the only result was a measure the total sales qty of ALL stores (21.000)

The relation between store and sales is based on d_store.

[dharmendars007 · Accepted solution]

Hello @dataanalyst123 

To ensure the measure dynamically reflects the filtered subset of stores from the slicers, you need to use CALCULATE() with ALLSELECTED() to respect the filters applied from your visuals or slicers.

Sales Qty all customers =
CALCULATE(SUM('Sales Table'[Qty]),ALLSELECTED('d_Store'))

This measure ensures that the sum of Qty only accounts for the stores remaining after filtering.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN