Create a dynamic column subtotal measure

tsunamisabella — Mon, 12 Jun 2023 09:21:48 GMT

Hello! I want create a measure [Subtotal] to retrieve the column subtotal (subtotal of the selected period) which can be dyanically changed along the selected column dimension [Period]. Note that [Period] is both the column dimension and a filter.

I created a measure below, however, in the pivot table, this [Subtotal] returns the grand total, instead of column subtotal per customer segment:

Subtotal = CALCULATE(
[a_measure],
ALLEXCEPT(table_name, table_name[Period])
)

[Period]	2023-01		2023-02		2023-03
[Customer]	Value	Subtotal	Value	Subtotal	Value	Subtotal	Column Subtotal
Segment 1	1	15	1	15	1	15	3
Segment 2	2	15	2	15	2	15	6
Segment 3	3	15	3	15	3	15	9
Segment 4	4	15	4	15	4	15	12
Segment 5	5	15	5	15	5	15	15

My goal it to have:

(1) the measure summarize the column subtotal at each row dimension

[Period]	2023-01		2023-02		2023-03
[Customer]	Value	Subtotal	Value	Subtotal	Value	Subtotal	Column Subtotal
Segment 1	1	3	1	3	1	3	3
Segment 2	2	6	2	6	2	6	6
Segment 3	3	9	3	9	3	9	9
Segment 4	4	12	4	12	4	12	12
Segment 5	5	15	5	15	5	15	15

(2) subtotal chanegs along [Period] selected, for exmaple, if I unselect 2023-03, the following table should return correct value:

[Period]	2023-01		2023-02
[Customer]	Value	Subtotal	Value	Subtotal	Column Subtotal
Segment 1	1	2	1	2	2
Segment 2	2	4	2	4	4
Segment 3	3	6	3	6	6
Segment 4	4	8	4	8	8
Segment 5	5	10	5	10	10

Can you help me correct my measure? Thank you in advance!

Re: Create a dynamic column subtotal measure

tamerj1 — Mon, 12 Jun 2023 09:29:49 GMT

Hi tsunamisabella

Please try

Subtotal = CALCULATE ( [a_measure], ALLSELECTED ( table_name[Period] ) )

Re: Create a dynamic column subtotal measure

tsunamisabella — Mon, 12 Jun 2023 09:47:09 GMT

OMG! Thank you for the swift reply and the simply & wonderful solution 🥰!

topic Create a dynamic column subtotal measure in DAX Commands and Tips

Create a dynamic column subtotal measure

Re: Create a dynamic column subtotal measure

Re: Create a dynamic column subtotal measure