Summarize error

[Thulasiraman]

Hi

I have created a SUMMARIZE Dax which shows the error which I could not understand.

Pls help

Thulasiraman

[MahyarTF]

Hi,

As I know the Summarized measure is a return table and not sure if you use it as a direct measure to calc the sum of column

https://dax.guide/summarize/

[Thulasiraman]

Hi @MahyarTF 

I would like to use it in TOPN.  Can you please tell how to write that dax?

[MahyarTF]

Hi

This is Dax code for creating the table using Summarize :

Sheet87Summarize = SUMMARIZE(sheet87, Sheet87[Customer], "sale", sum(Sheet87[Weight]))

This is TOp N with Summarize :

Sheet87TopSum = topn(2, SUMMARIZE(sheet87, Sheet87[Customer], "Sales", sum(Sheet87[Weight])))

Appreciate your Kudos and please mark it as a solution if it helps you

[Thulasiraman]

[Thulasiraman]

Hi @MahyarTF 

Created the above TOPN dax.  

Tested the Summarize portion and it creates a valid table.

But when nested in TOPN it shows the error.

Appreciate very much if you could be able to find the error I am committing.

[FreemanZ]

Same issue: TOPN returns a list/table. When creating a measure, the outmost function shall be someone returning a value (or a one-row-one-column table), like SUM, MIN/MAX/AVERAGE, COUNTROWS, MINX/MAXX, etc.

try like:

measure = CALCULATE( SUM[tbl[Value]], TOPN(...))

What is your ultimate goal of this measure?

[Thulasiraman]

Hi @FreemanZ

Thanks for your continued support and clarifications.

I have successfully created a table using the summarize function.  

Sum Table = SUMMARIZE('Itemwise DC All', 'Itemwise DC All'[Dye Item], "cost", SUM('Itemwise DC All'[Tally amt]))

Now this table contains 2 columns 1) the name of the item (dye item) and 2) the amount (tally amt) column called 'cost'

I want to find the Top N of the above item based on the cost.

I tried using the calculate function as below which is not giving the desired result:

TOPN = CALCULATE(SUM(sum table[cost]), TOPN(3, sum table, SUM(sum table[cost])

It gives the total values against all the items.

Once again thanks for your patient explanations.

[FreemanZ]

Hi @Thulasiraman 

are you creating a measure?

[Thulasiraman]

Hello @FreemanZ 

Yes, created a measure.

[FreemanZ]

hi @Thulasiraman 

SUMMARIZE returns a table, and, in most cases, a table can't be used as a scalar value directly. So comes the warning.

You need either make some calculation based on that table or use the code to create a new table.

[Thulasiraman]

Hi @FreemanZ 

Thanks for the explanation.  Can I use this dax in TOPN function?

[FreemanZ]

hi @Thulasiraman Yes, as you see in @MahyarTF 's code, SUMMARIZE is every commonly used to build intermediate virtual tables for furthe calculation, like TOPN, ADDCOLUMNS, FILTER. Any function taking table expression as argument is good to nest upon SUMMARIZE.

[Thulasiraman]

Hi @FreemanZ 

I nested in topn function but it shows error.  I posted the screenshot above.

[Thulasiraman]

Hi @FreemanZ and @MahyarTF 

In continuation of above, after referring some material I have created the following for TopN which is working fine:

Top3 DC =

VAr ranking = VALUES('Itemwise DC All'[Dye Item])

Return

CALCULATE([Total cost], TOPN( 3, ALL('Itemwise DC All'[Dye Item]), [Total cost]), ranking)

There is no creation of virtual table through SUMMARIZE but the above dax gives correct result so far for 3 of my tables which I tried.

I request you to kindly validate the above dax if it is correct and applicable in all conditions.

Once again thanking you very much for your patient help.