Forum Discussion
New DAX table with count summary
Hi all, I'm trying to create a new table pulling through some data from an existing table (using a semantic model I don't have power query access to). The columns I want to use are the following:
- Week commencing
- Order number
- Area
I've managed to pull through the week commencing with the count of orders and the area for each but it's counted all the orders in the table and repeated them on each line, what I need it to do is show the count for that week and area instead. Can anyone show me how to do this please? Thank you!
Current table using DAX:
Table = ADDCOLUMNS(
SUMMARIZE(
'Sales',
'Sales'[Week Commencing],
'Sales'[Area]),
"Total orders", CALCULATE(COUNT('Sales'[Order]), FILTER('Sales', 'Sales'[Week Commencing]) ))
| Week commencing | Total orders | Area |
| 01/04/2025 | 3300 | North |
| 01/04/2025 | 3300 | East |
| 01/04/2025 | 3300 | South |
| 01/04/2025 | 3300 | West |
| 08/04/2024 | 3300 | North |
| 08/04/2024 | 3300 | East |
| 08/04/2024 | 3300 | South |
| 08/04/2024 | 3300 | West |
I need it to look more like this if possible!
| Week commencing | Total orders | Area |
| 01/04/2025 | 134 | North |
| 01/04/2025 | 120 | East |
| 01/04/2025 | 54 | South |
| 01/04/2025 | 128 | West |
| 08/04/2024 | 26 | North |
| 08/04/2024 | 79 | East |
| 08/04/2024 | 81 | South |
| 08/04/2024 | 109 | West |
Hello samc_26
SUMMARIZE(...) creates a unique row for each Week Commencing + Area combo.
ALLEXCEPT(...) removes filters on all other columns except Week Commencing and Area, allowing CALCULATE to count only the orders in that slice.
Can you try this:
Table =
ADDCOLUMNS(
SUMMARIZE(
'Sales',
'Sales'[Week Commencing],
'Sales'[Area]
),
"Total orders",
CALCULATE(
COUNT('Sales'[Order]),
ALLEXCEPT('Sales', 'Sales'[Week Commencing], 'Sales'[Area])
)
)Hi samc_26
Can you please try the below DAX ?
Table =
ADDCOLUMNS(
SUMMARIZE('Sales', 'Sales'[Week Commencing], 'Sales'[Area]),
"Total Orders", CALCULATE(
COUNT('Sales'[Order]),
ALLEXCEPT('Sales', 'Sales'[Week Commencing], 'Sales'[Area])
)
)
If this answers your questions, kindly accept it as a solution and give kudos.
5 Replies
Hello samc_26
SUMMARIZE(...) creates a unique row for each Week Commencing + Area combo.
ALLEXCEPT(...) removes filters on all other columns except Week Commencing and Area, allowing CALCULATE to count only the orders in that slice.
Can you try this:
Table =
ADDCOLUMNS(
SUMMARIZE(
'Sales',
'Sales'[Week Commencing],
'Sales'[Area]
),
"Total orders",
CALCULATE(
COUNT('Sales'[Order]),
ALLEXCEPT('Sales', 'Sales'[Week Commencing], 'Sales'[Area])
)
)Thank you very much this worked great, much appreciated!! 👏
Hi anilelmastasi just wondering if you can help me again please as I realised I need to add some filters into this table which I don't need to see on the output but they need to be taken into consideration in the count of order final figure. How do I add filters to the result in this table?
I've been googling and experimenting with the code and all I've managed to do is add the columns I need to filter into the table but then that doesn't sum up the orders by week commencing properly, I get multiple copies of week commencing with the figures split out.
If you could help me with this I would be very appreciative! Just wish I could do all this myself! Thank you
Hi samc_26
Can you please try the below DAX ?
Table =
ADDCOLUMNS(
SUMMARIZE('Sales', 'Sales'[Week Commencing], 'Sales'[Area]),
"Total Orders", CALCULATE(
COUNT('Sales'[Order]),
ALLEXCEPT('Sales', 'Sales'[Week Commencing], 'Sales'[Area])
)
)
If this answers your questions, kindly accept it as a solution and give kudos.Both of these solutions worked so thank you very much! 👏
