topic Sum based on Active Filters in DAX Commands and Tips

Sum based on Active Filters

zhejun728 — Sun, 08 Sep 2024 08:04:59 GMT

I'm a user of Tableau but a totally new user of Power BI.

Im trying to create a new column to show the sum of COST which can be affected by active filters on visual.

I have a sample data as below.

item	ITEM GROUP	COST
SEATCOVER	VICTRON	9
WASHER	GCMSB	0.5
NUT	GCMSB	1
COLLAR	CW	5
SHAFT	GCMSB	7
COVER	BL	10

My expected outcome in Power BI whgen no active filters applied.

item	ITEM GROUP	COST	Total Cost
SEATCOVER	VICTRON	9	32.5
WASHER	GCMSB	0.5	32.5
NUT	GCMSB	1	32.5
COLLAR	CW	5	32.5
SHAFT	GCMSB	7	32.5
COVER	BL	10	32.5

When ITEM GROUP is not GCMSB.

item	ITEM GROUP	COST	Total Cost
SEATCOVER	VICTRON	9	24
COLLAR	CW	5	24
COVER	BL	10	24

If possible, I wish there is no hard coding in the command line, and can works dynamically with the active filters.
Thanks !!

Re: Sum based on Active Filters

tamerj1 — Sun, 08 Sep 2024 08:46:25 GMT

Hi zhejun728

please try

Total Cost =
CALCULATE ( [Cost], ALLSELECTED ( ) )

Re: Sum based on Active Filters

Anonymous — Tue, 10 Sep 2024 01:58:34 GMT

Hi zhejun728 ,

I create a table as you mentioned. Also I created a Slicer.

As tamerj1 said, you can create a measure and here is the DAX code.

Total Cost = CALCULATE ( SUM ( 'Table'[COST] ), ALLSELECTED ( 'Table' ) )

Finally you can get what you want.

Best Regards

Yilong Zhou

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

Re: Sum based on Active Filters

zhejun728 — Thu, 12 Sep 2024 04:03:31 GMT

Thank Yilong ! That's help !!

Re: Sum based on Active Filters

zhejun728 — Thu, 12 Sep 2024 04:04:00 GMT

Thanks tamerj1 😁