DAX to pick field with highest value within calculated table

[Anonymous]

Hi PowerBI community,

I have created a calculated table to summarize many fields, just adding them within the “summarize” function OR calculating new fields based on some conditions inside the “calculate function”.

All that is working well. The calculated table I created is based on a field called “product” (“based” means that every single line in that table correspond to one product).

My question (two actually):  (1) how can I add to that calculated table a calculated new field "Main color" that picks the highest “color” for each product based on the input “spend” field – while keeping the output “spend” field the sum of all values from the input table. In addition, (2) I want another new field "Main color spend" with the spend for the main color only.

See below what I am trying to do:

A millon thanks for whoever can help with this rather complex dax code within a calculated table.

Thanks for your help!

Kevin

[tamerj1 · Accepted solution]

Hi @Anonymous 

Please refer to attached sample file with the solution

Output Table =
VAR T =
    SUMMARIZE (
        'Input Table',
        'Input Table'[Product],
        'Input Table'[Color],
        "@Spend", SUM ( 'Input Table'[Spend] )
    )
RETURN
    GENERATE (
        SUMMARIZE ( T, [Product] ),
        VAR CurrentProduct = [Product]
        VAR T1 =
            FILTER ( T, [Product] = CurrentProduct )
        VAR T2 =
            TOPN ( 1, T1, [@Spend] )
        VAR MainColor =
            MAXX ( T2, [Color] )
        VAR MainColorSpend =
            MAXX ( T2, [@Spend] )
        VAR TotalSpend =
            SUMX ( T1, [@Spend] )
        RETURN
            ROW (
                "Main Color", MainColor,
                "Main Color Spend", MainColorSpend,
                "Total Spend", TotalSpend
            )
    )

[Anonymous]

Thank you very much! As I already had a big calculated table, I've created an additional one as per your code. Then connected them via keys and in the end it worked! Thanks again!!

Kevin

[Anonymous]

Hi  kevincc2 

You can create three columns in table

Count_color = COUNTAX(FILTER('Table (5)','Table (5)'[Product]=EARLIER('Table (5)'[Product])&&'Table (5)'[Color]=EARLIER('Table (5)'[Color])),[Color])



Max_color = MAXX(FILTER('Table (5)','Table (5)'[Product]=EARLIER('Table (5)'[Product])),[Count_color])



Sum_product = SUMX(FILTER('Table (5)','Table (5)'[Product]=EARLIER('Table (5)'[Product])),[Spend])

Then create a new table

Table 4 = var a=FILTER('Table (5)','Table (5)'[Count_color]='Table (5)'[Max_color])

var b= SUMMARIZE(a,[Product],[Color],[Spend],[Sum_product],"Main color",[Color],"Main color spend",SUMX(FILTER(a,[Product]=EARLIER('Table (5)'[Product])),[Spend]),"Total Spend",[Sum_product])

return SUMMARIZE(b,[Product],[Main color],[Main color spend],[Total Spend])

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[tamerj1]

Hi @Anonymous 

Please try

Output Table =
VAR T =
SUMMARIZE (
'Input Table',
'Input Table'[Product],
'Input Table'[Color],
"@Spend", SUM ( 'Input Table'[Spend] )
)
RETURN
GENERATE (
SUMMARIZE ( T, [Product] ),
VAR CurrentProduct = [Product]
VAR T1 =
FILTER ( T, [Product] = CurrentProduct )
VAR T2 =
TOPN ( 1, T1, [@Spend] )
VAR MainColor =
MAXX ( T2, [Color] )
VAR MainColorSpend =
MAXX ( T2, [@Spend] )
VAR TotalSpend =
SUMX ( T1, [@Spend] )
RETURN
ROW (
"Main Color", MainColor,
"Main Color Spend", MainColorSpend,
"Total Spend", TotalSpend
)
)

[amitchandak]

@Anonymous , You need new columns

Color count = countx(filter(Table,[Product] = earlier([Product])  && [Color] = Earlier([Color]) ), [Product])

Max color =

var _max = maxx(filter(Table, [Product] = earlier([Product])  ), [Color count])

return

maxx(filter(Table, [Product] = earlier([Product]) &&  [Color count] = _cnt) , [Color])

The measures you need with max color as view by

main color sepnd = sumx(filter(Table, [Color] = [Max COlor]), [Spend])

total spend = sum(Table[Spend])

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8