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
orangeatom
Resolver I
Resolver I

Best approach to rank + ratio of measures

Hello all,

I need some guidance on creating some measures to deliver a KPI.

I'd like to take the top n customers (denominator) and find out how many of those customers are selling more than x dollars (numerator). Then I will need to present that as a ratio. What is the best way in approaching this?

Direction will be greatly appreciated!

1 ACCEPTED SOLUTION
orangeatom
Resolver I
Resolver I

I have found how to accomplish my ambigous request but i'm not sure if this is the most efficient method:

 

1 - Use the SUMMARIZEDCOLUMNS function with a filter using RANKX

entity =
FILTER(
SUMMARIZECOLUMNS(
entity[attribute],
"column",[attribute]
),
RANKX (
ALL ( entity[attribute] ),
[attribute]
) <= n
)

2 - Create a column flagging x dollars

Flag =
IF(
[attribute] > 0,
1,
0
)

3 - Create measures to get performance

Ratio = ([Indicator]/n)*100

View solution in original post

2 REPLIES 2
orangeatom
Resolver I
Resolver I

I have found how to accomplish my ambigous request but i'm not sure if this is the most efficient method:

 

1 - Use the SUMMARIZEDCOLUMNS function with a filter using RANKX

entity =
FILTER(
SUMMARIZECOLUMNS(
entity[attribute],
"column",[attribute]
),
RANKX (
ALL ( entity[attribute] ),
[attribute]
) <= n
)

2 - Create a column flagging x dollars

Flag =
IF(
[attribute] > 0,
1,
0
)

3 - Create measures to get performance

Ratio = ([Indicator]/n)*100

@orangeatom,

Have you got expected result via the above method? The formulas you post are OK in my opinion. You can accepe your reply as answer,  this way, other community members would easily find the answer when  they get same issues.

Regards,
Lydia

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

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.