Forum Discussion
Create a dynamic column subtotal measure
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!
Please try
Subtotal = CALCULATE ( [a_measure], ALLSELECTED ( table_name[Period] ) )
2 Replies
Please try
Subtotal = CALCULATE ( [a_measure], ALLSELECTED ( table_name[Period] ) )OMG! Thank you for the swift reply and the simply & wonderful solution 🥰!
