topic Re: Using TOPN not giving expected result in DAX Commands and Tips

Using TOPN not giving expected result

Anonymous — Thu, 16 Dec 2021 12:45:07 GMT

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!

Re: Using TOPN not giving expected result

smpa01 — Thu, 16 Dec 2021 15:38:27 GMT

Anonymous SUMMARIZECOLUMNS can't be utilized in a measure. It only works in DAX table expression.

Re: Using TOPN not giving expected result

AlexisOlson — Thu, 16 Dec 2021 16:45:54 GMT

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

Re: Using TOPN not giving expected result

Anonymous — Fri, 17 Dec 2021 07:21:21 GMT

Thx to both. Had missunderstood "SUMMARIZECOLUMNS" a bit outside queries 😀
Your edit gives the exact total as i was looking for, Thank you.