New DAX table with count summary

[samc_26]

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

[anilelmastasi · Accepted solution]

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])
)
)

[mdaatifraza5556 · Accepted solution]

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.

[samc_26]

Both of these solutions worked so thank you very much! 👏

[anilelmastasi · Accepted solution]

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])
)
)

[samc_26]

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

[samc_26]

Thank you very much this worked great, much appreciated!! 👏