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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Rishabh-Maini
Helper II
Helper II

Top N values including Ties

Hi, 

I have the following table:

 

Capture.PNG

I want to display the days with maximum sale, including all days if it is a tie.
In the above example, I wish to display "Mon, Thur" in this format. 

I DO NOT want a table with two values. I want to display a card with these two values separated by a comma. (and so on and so forth if there are 3 days with the same sale amount), something like this:

RishabhMaini_0-1629753412098.png

 



Is this possible in Power BI at all?

Thanks!

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@Rishabh-Maini,

 

Try this measure:

 

Highest Sale = 
VAR vTable =
    ADDCOLUMNS (
        VALUES ( Table1[Day] ),
        "@Rank", RANKX ( ALL ( Table1[Day] ), CALCULATE ( SUM ( Table1[Sale] ) ),, DESC, DENSE )
    )
VAR vTopValues =
    FILTER ( vTable, [@Rank] = 1 )
VAR vResult =
    CONCATENATEX ( vTopValues, Table1[Day], ", " )
RETURN
    vResult

 

DataInsights_0-1629756975002.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
DataInsights
Super User
Super User

@Rishabh-Maini,

 

Try this measure:

 

Highest Sale = 
VAR vTable =
    ADDCOLUMNS (
        VALUES ( Table1[Day] ),
        "@Rank", RANKX ( ALL ( Table1[Day] ), CALCULATE ( SUM ( Table1[Sale] ) ),, DESC, DENSE )
    )
VAR vTopValues =
    FILTER ( vTable, [@Rank] = 1 )
VAR vResult =
    CONCATENATEX ( vTopValues, Table1[Day], ", " )
RETURN
    vResult

 

DataInsights_0-1629756975002.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@DataInsights 
You, are a blessing. 
Thank you so much!

While here, could you suggest any websites/sources where I can learn/practice/explore DAX measures (instead of only working on them when a requirement rolls in)? Would really appreciate it. 

@Rishabh-Maini,

 

Check out these sites:

 

https://www.sqlbi.com (check out their book "The Definitive Guide to DAX")

https://enterprisedna.co

https://radacad.com

 

DAX is a tricky language that requires a conceptual understanding. Invest the time necessary to learn concepts, and measures will flow more easily. 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@DataInsights Thanks once again!

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.