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
amuola
Helper II
Helper II

RANKX of a measure

 

Hi,

 

I am trying to rank the average duration of a flow, based on the below table (just some random numbers for illustration, I have many more columns in the original file). I have different products (tagged with ID’s = first column), and each product belongs to a certain category (“Type” = second column). Each product flows through different stages, and time is measured at each stage. A stage is equal to one row in the table.

 

IDTypeDurationDays
1A4
1A9
1A4
1A4
1A9
2B2
2B7
2B1
3A1
3A3
3A5
3A2
4A8
4A8
4A5
4A5
5B6
5B3
6C10
6C2
6C8

 

So product ID 1 in this example, goes through five stages and total time elapsed is 30 days, ID nr 2 goes through three stages and total time is 10 and so forth.

 

I have calculated the total duration:

SumDuration = SUM(Duration[DurationDays])

 

Then I calculated the average duration (also as a measure):

AverageDuration = CALCULATE(SUMX(Duration;[SumDuration])/DISTINCTCOUNT(Duration[ID]))

 

Now I want to rank the Type/ ID by the AverageDuration, but I cannot find out what I am doing wrong:

RankX = IF(

                               HASONEVALUE(Duration[Type]);

                                               RANKX(ALL(Duration);Duration[AverageDuration])

)

 

Result from RankX:

RankX.JPG

 

Appreciate any help!

 

Regards

Amund

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @amuola,

 

Please modify the RankX formula as:

RankX =
RANKX (
    ALL ( Duration ),
    CALCULATE (
        SUM ( Duration[DurationDays] ),
        ALLEXCEPT ( Duration, Duration[Type] )
    ),
    ,
    DESC,
    DENSE
)

You will get rank result:

1.PNG

 

If above result doesn't meet your requirement, please post an image to show your expected output. By the way, I was confused about why you said "product ID 1 goes through five stages and total time elapsed is 15 days, ID 2 goes through three stages and total time is 21", how to generate the total time 15 and 21? Also, it looks like in matrix, you didn't refer to these total days.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

Hi @amuola,

 

Please modify the RankX formula as:

RankX =
RANKX (
    ALL ( Duration ),
    CALCULATE (
        SUM ( Duration[DurationDays] ),
        ALLEXCEPT ( Duration, Duration[Type] )
    ),
    ,
    DESC,
    DENSE
)

You will get rank result:

1.PNG

 

If above result doesn't meet your requirement, please post an image to show your expected output. By the way, I was confused about why you said "product ID 1 goes through five stages and total time elapsed is 15 days, ID 2 goes through three stages and total time is 21", how to generate the total time 15 and 21? Also, it looks like in matrix, you didn't refer to these total days.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank’s for the input. There was a wrong calculation in the numbers (as you questioned), which I have now corrected. The sum I am referring to is the DurationDays added together for each of the ID’s.

 

I will modify the formula and try to use it in my real dataset. Thanks a lot!

 

Regards

Amund

Greg_Deckler
Super User
Super User

This sounds similar to a design pattern I published on Groups and SuperGroups. Check out the article here:

 

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

 

I believe the solution will involve using RANKX along with a SUMMARIZE.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

Thank you for the reply, I will look closer into this.

 

Regards

Amund

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.