Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
ID | Type | DurationDays |
1 | A | 4 |
1 | A | 9 |
1 | A | 4 |
1 | A | 4 |
1 | A | 9 |
2 | B | 2 |
2 | B | 7 |
2 | B | 1 |
3 | A | 1 |
3 | A | 3 |
3 | A | 5 |
3 | A | 2 |
4 | A | 8 |
4 | A | 8 |
4 | A | 5 |
4 | A | 5 |
5 | B | 6 |
5 | B | 3 |
6 | C | 10 |
6 | C | 2 |
6 | C | 8 |
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:
Appreciate any help!
Regards
Amund
Solved! Go to Solution.
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:
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
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:
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
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
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.
Hi,
Thank you for the reply, I will look closer into this.
Regards
Amund
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |