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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
SomeDataDude
Advocate I
Advocate I

How to rank sales on one line

Hello,

 

I need help formulating dax measure. The image below shows that for OrderID 1717694 the amount is 696.67. This is now shown on all 5 lines. However, I only want to see this on 1 line.

 

SomeDataDude_0-1662468600707.png

This is the output I am looking for:

SomeDataDude_1-1662469080426.png

 

In SQL I use the following code to organize this:

 

 

 

iif(
     RANK () 
      OVER (  
       PARTITION BY Table1.invkey 
        ORDER BY Table1.OrderID, Table2.ordsubtaskno, Table3.goodid DESC ) =1, 
          Table4.Amounttwv,0) 'TotalAmount',

 

 

 

How do i do this with dax. Or is this not possible?

I've already tried it with RankX and Rank.EQ. But probably I'm not applying it right.

1 ACCEPTED SOLUTION

Hi again, 

It will be something like this? 

onurbmiguel__0-1662642797400.png

 

the DAX for the new measure: 

 

TotaalAmount 2 = 
var _id = SELECTEDVALUE(Query1[OrderId])
var _control = 
    CALCULATE(
        min(Query1[DeliveryRealTime]),
        FILTER(ALL(Query1),Query1[OrderId]= _id))
return 
if(min(Query1[DeliveryRealTime]) = _control, [TotaalAmount], BLANK())

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


View solution in original post

4 REPLIES 4
onurbmiguel_
Power Participant
Power Participant

Hi SomeDataDude

Can you share some sample of your data, to understand how you have structure the information.

 

BR 

Bruno 

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Hi @onurbmiguel_ 

 

Thanks for replying. 

 

I have prepared an example file. In this example, I have merged multiple tables in Query1. The amount is taken from table Query2.

 

If you have any questions, let me know.

 

You can download the pbix file via the following link: https://we.tl/t-gDSbxD7qs5 

 

Hi again, 

It will be something like this? 

onurbmiguel__0-1662642797400.png

 

the DAX for the new measure: 

 

TotaalAmount 2 = 
var _id = SELECTEDVALUE(Query1[OrderId])
var _control = 
    CALCULATE(
        min(Query1[DeliveryRealTime]),
        FILTER(ALL(Query1),Query1[OrderId]= _id))
return 
if(min(Query1[DeliveryRealTime]) = _control, [TotaalAmount], BLANK())

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Hi, 

 

Yes, this works!

 

Thank you very much!

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors