Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Soof1234
Helper I
Helper I

Display the value with the highest ranking from a measure in a card visual

Hi,

 

Struggling to find the correct measure for my problem:

 

I have created following tablevisual:

 

Soof1234_0-1674132368340.png

 

Intakes = created with following measure:
AantalIntakes = COUNT(Intakes[Id])

 

Rank is created with following measure: 

Ranking medewerkers = RANKX(ALL(Intakes[Sourcers.Title]),[AantalIntakes])
 
Values in column Naam comes from table/column 'Intakes'[Sourcers.Title]
 
Now I want to create a measure to display the Name with the highest amount of intakes , a measure with the second highest and a measure with the third highest.
 
All of these measures will be put into a separate card visual. See below for the desired outcome:
 
Soof1234_2-1674132637510.png

 

Above the names I will create a measure to display the beloning count of intakes and above that I will put their profilepic.

 

Would be very grateful if anyone could give me the desired solution.


Kind Regards,

 

Soof1234

 

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

Here are 3 measures that show one way to do it.

 

ppm1_0-1674133288509.png

#1 = 
VAR vSummary =
    ADDCOLUMNS ( VALUES ( Products[ProductName] ), "cTS", [Total Sales] )
RETURN
    MAXX (
        TOPN ( 1, vSummary, [cTS], ASC ),
        Products[ProductName]
    )

#2 = 
VAR vSummary =
    ADDCOLUMNS ( VALUES ( Products[ProductName] ), "cTS", [Total Sales] )
RETURN
    MAXX (
        TOPN ( 1, TOPN ( 2, vSummary, [cTS], DESC ), [cTS], ASC ),
        Products[ProductName]
    )
#3 = 
VAR vSummary =
    ADDCOLUMNS ( VALUES ( Products[ProductName] ), "cTS", [Total Sales] )
RETURN
    MAXX (
        TOPN ( 1, TOPN ( 3, vSummary, [cTS], DESC ), [cTS], ASC ),
        Products[ProductName]
    )

 

Pat

 

Microsoft Employee

View solution in original post

5 REPLIES 5
ppm1
Solution Sage
Solution Sage

Here are 3 measures that show one way to do it.

 

ppm1_0-1674133288509.png

#1 = 
VAR vSummary =
    ADDCOLUMNS ( VALUES ( Products[ProductName] ), "cTS", [Total Sales] )
RETURN
    MAXX (
        TOPN ( 1, vSummary, [cTS], ASC ),
        Products[ProductName]
    )

#2 = 
VAR vSummary =
    ADDCOLUMNS ( VALUES ( Products[ProductName] ), "cTS", [Total Sales] )
RETURN
    MAXX (
        TOPN ( 1, TOPN ( 2, vSummary, [cTS], DESC ), [cTS], ASC ),
        Products[ProductName]
    )
#3 = 
VAR vSummary =
    ADDCOLUMNS ( VALUES ( Products[ProductName] ), "cTS", [Total Sales] )
RETURN
    MAXX (
        TOPN ( 1, TOPN ( 3, vSummary, [cTS], DESC ), [cTS], ASC ),
        Products[ProductName]
    )

 

Pat

 

Microsoft Employee

Hi @ppm1 ,

 

Thanks, you are smarter than ChatGPT;) I changed [Total sales] to [ranking] instead in the measures. This because total sales could be the same value. However in my current setup people get the same ranking instead of making a continuous ranking when having the same value. See example below:

 

Soof1234_0-1674135762685.png

 

Above tablevisual results in below stage:

 

Soof1234_1-1674135840969.png

 

Obviously it needs to display Esmee either first or second. Thanks in advance!

 

Kind Regards,

 

Soof1234

 

Hi,

For Position 1, wouldn't you want to show "Esme, Francios"?  Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Here is one way to break those ties (alphabetically).

 

#1 = 
VAR vSummary =
    ADDCOLUMNS ( VALUES ( Products[ProductName] ), "cTS", [Total Sales] )
RETURN
    MAXX (
        TOPN ( 1, vSummary, [cTS], ASC, Products[ProductName], ASC ),
        Products[ProductName]
    )

 

Pat

Microsoft Employee

Hi @ppm1 ,

 

Sorry, I forgot to mark your post as a solution. Probably over excited you made it work:). Thanks!

 

Do you also know how I can add a belonging image/profilepicture to the shape above the stage of position 1,2,3. 

 

I tried conditionnal formatting and then select value and base the value on a measure I created. The measure retrieves an imageURL from column 'ProfilePic' in table Employees. Column 'ProfilePic.Url'  is marked as an ImageURL column.

 

I added a sample file as PBIX. Hopefully you have the solution:)

 

Thanks in advance!

 

Kind Regards,

 

Soof1234

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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