LOOKUPVALUE of the factor with the highest value

[jobf]

Hello. I needed to create a measure to serve as a tooltip for a chart that showed the best-selling factor in that period. For example:

Period	Factor	Sales
PE1	P1	120
PE1	PM2	139
PE2	P2	134
PE2	PM1	145
PE3	P3	98
PE3	PM3	123

I had created a measure with the following code:

measure = LOOKUPVALUE('table'[Variety],'table'[Sales],max('table'[Sales]))

It worked well overall on the chart. But for some reason, when I filtered a specific factor, the chart would give an error, maybe because some factors did not have sales in all periods.
Anyway, I would appreciate if you could give me some ideas for new measures. A measure that shows which factor made more sales.

[Ashish_Mathur]

Hi,

This measure works

Tooltip = CONCATENATEX(TOPN(1,VALUES(Data[Factor]),[S]),Data[Factor]&" – "&FORMAT([s],"0")," | ",[s], DESC)

Hope this helps.

[Irwan]

hello @jobf 

is this what you are looking for?

the value is still showing the highest value of selected Period.

the cursor is hovering at 120 in PE1 but the highest is 139 for PE1 so it shows 139.

if yes, then please check this measure DAX.

More Sales = 
var _Period = SELECTEDVALUE('Table'[Period])
Return
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Period]=_Period
    ),
    'Table'[Sales]
)

Hope this will help.

Thank you.

[jobf]

No. The measure would be to show which factor made the largest number of sales in the period. For example, in period PE1, the factor that made the largest number of sales was PM2. In period PE2, the factor that made the largest number of sales was PM1. The measure is to show which factor made the largest number of sales.

[Irwan · Accepted solution]

hello @jobf 

i see, so it show factor instead of sales.

then you can change the expression MAXX from Sales into Factor.

More Sales = 
var _Period = SELECTEDVALUE('Table'[Period])
Return
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Period]=_Period
    ),
    'Table'[Factor]
)

Hope this will help.

Thank you.

[jobf]

Okay, now it's showing the factor, but there's a problem. The real table has several columns with numbers. I think the code is showing the factor whose value in all columns is the highest. Is there a way to specify in the code that it should show which factor had the highest sale value

[Irwan]

Hello @jobf 

Yes, it will calculate factor which has the highest value within same period since the applied filter only for period.

You can add them as filter. But please remember to add them as variable so it can get calculated.

Something like below

More Sales = 

var _Period = SELECTEDVALUE('Table'[Period])

var _1stFilter = SELECTEDVALUE('Table'[Filter1])

Return

MAXX(

    FILTER(

        ALL('Table'),

        'Table'[Period]=_Period&&Filter1&&(and so on..)

    ),

    'Table'[Factor]

)

Hope this will help.

Thank you.