cancel
Showing results for 
Search instead for 
Did you mean: 
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

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 

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

Hi, 

 

Yes, this works!

 

Thank you very much!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors