Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to 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
Hi,
Here is one way to do this:
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/
Proud to be a Super User!
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?
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
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.
Proud to be a Super User!