Forum Discussion
New DAX table with count summary
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.
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 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