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 two takeaways i want to use the measure for:
- Getting the actual value for what ever "TOPN"-n i use.
- Hold the values of "Postal Code" in new VAR for alternative measuer.
My Measure:
#Top 20 PostalCodes SalesNet =
VAR _Exclude =
FILTER(
KEEPFILTERS(VALUES('Customer'[Customer Postal Code])),
'Customer'[Customer Postal Code] <> "n/a"
)
VAR _SalesPerCode =
SUMMARIZECOLUMNS(
Customer[Customer Postal Code],
_Exclude,
"Sales Net",[Sales Net]
)
VAR _TOP20 =
TOPN(20,_SalesPerCode,0)
VAR _Core =
SUMMARIZECOLUMNS(
Customer[Customer Postal Code],
_Exclude,
_TOP20,
"Sales_Net", [Sales Net]
)
RETURN
SUMX(_Core, [Sales_Net])For the 1 point, I'm stuck on understanding why the this gives me the following error:
For the 2 point im just plain stuck on how to solve this. When looking at this in DaxStudio, i get a table of the top sales net per postal code:
I am convinced that I should be able to hold the "Postal code" to be used further in a variable.
Grateful for all help!
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] )
3 Replies
Anonymous SUMMARIZECOLUMNS can't be utilized in a measure. It only works in DAX table expression.
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] )
