values from Order_By column within Summar table

[DavidGolden]

Hi,

I used summarize in the (sales) table, and grouped by columns [Cod] and [Sec], being the summary expressión "prima" a simple sum. The virtual summarized table would look like this.

Cod	Sec	prima
1542	1	$1.425,75
1542	4	$2.722,30
1724	3	$2.524,11
1724	8	$241,00

Now, from this virtual summarized table,  I need the get the values of [Sec] colum for the max (prima) for each [Cod], that woul be "SecOfMaxPrima". The returned value would a table containing at least  columns [Cod] and [SecOfMaxPrima]  

In this example, returned values should be 

Cod	Sec
1542	4
1724	3

I failed at using lookupvalue for this, since it doesn´t recognize the virtual summary table. I failed also at summarizing the summarized table (can you do that?)

Would you please help me solve this. Thanks!

[ValtteriN]

Hi,

Here is one way to do this:

Table 7 = SUMMARIZE(SecCod,SecCod[cod],"sec",

CALCULATE(MAX(SecCod[sec]),ALLEXCEPT(SecCod,SecCod[cod]),SecCod[prima]=MAX(SecCod[prima])))

Example:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[DavidGolden]

Thnaks @ValtteriN , can I do that within a virtual table taht has been already summarized?

I mean, the 'SecCod' table you suggest is actually a returned table from summarize function, I tryed your suggestion, but it didnt worked. Am I missing something?

[v-shex-msft · Accepted solution]

Hi @DavidGolden,

Did you mean the sample table that you shared has been summarized already? If that is the case, you can try to use the following formulas to create a new table if it is suitable for your requirement:

NewTable =
VAR summary =
    SUMMARIZE ( 'Table', [Cod], [Sec], "s_prima", SUM ( 'Table'[prima] ) )
VAR mPrima =
    GROUPBY ( summary, [Cod], "prima", MAXX ( CURRENTGROUP (), [s_prima] ) )
RETURN
    FILTER ( summary, [s_prima] IN SELECTCOLUMNS ( mPrima, "prima", [prima] ) )

Regards,

Xiaoxin Sheng

[ValtteriN]

Hi,

You can place your previous DAX within a variable and summarize it. Do you want to have your end result as a measure or as a table? I am not sure I understood your goal correctly.