Forum Discussion
Using TOPN not giving expected result
Hi Experts First I need to mention that this is through a live connection. All have a correct connection. After reading several posts, I thought my measure was ready for the task, but no. There are...
As smpa01 mentioned, while SUMMARIZECOLUMNS is great for DAX queries but isn't useful inside measures.
I don't think you need it here though. Try this:
#Top 20 PostalCodes SalesNet = VAR _Exclude = FILTER ( KEEPFILTERS ( VALUES ( 'Customer'[Customer Postal Code] ) ), 'Customer'[Customer Postal Code] <> "n/a" ) VAR _SalesPerCode = ADDCOLUMNS ( _Exclude, "@SalesNet", [Sales Net] ) VAR _TOP20 = TOPN ( 20, _SalesPerCode, [@SalesNet] ) RETURN SUMX ( _TOP20, [@SalesNet] )
As smpa01 mentioned, while SUMMARIZECOLUMNS is great for DAX queries but isn't useful inside measures.
I don't think you need it here though. Try this:
#Top 20 PostalCodes SalesNet =
VAR _Exclude =
FILTER (
KEEPFILTERS ( VALUES ( 'Customer'[Customer Postal Code] ) ),
'Customer'[Customer Postal Code] <> "n/a"
)
VAR _SalesPerCode = ADDCOLUMNS ( _Exclude, "@SalesNet", [Sales Net] )
VAR _TOP20 = TOPN ( 20, _SalesPerCode, [@SalesNet] )
RETURN
SUMX ( _TOP20, [@SalesNet] )
Thx to both. Had missunderstood "SUMMARIZECOLUMNS" a bit outside queries 😀
Your edit gives the exact total as i was looking for, Thank you.