cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## LOOKUPVALUE of the factor with the highest value

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.
1 ACCEPTED SOLUTION
Solution Sage

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])ReturnMAXX(    FILTER(        ALL('Table'),        'Table'[Period]=_Period    ),    'Table'[Factor])`

Hope this will help.

Thank you.

6 REPLIES 6
Super User

Hi,

This measure works

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Sage

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])ReturnMAXX(    FILTER(        ALL('Table'),        'Table'[Period]=_Period    ),    'Table'[Sales])`

Hope this will help.
Thank you.
Helper I

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.

Solution Sage

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])ReturnMAXX(    FILTER(        ALL('Table'),        'Table'[Period]=_Period    ),    'Table'[Factor])`

Hope this will help.

Thank you.

Helper I

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

Solution Sage

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors