TOP N Customers by profit (N is a measure output)

[Anonymous]

Hello PowerBI Community,

What I have in mind is a measure that reorders the customer list according to the profit I make off of them. The measure then filters out TOP X customers (the X is from a different measure) and displays just them in a table. Seems pretty simple but I could not find any info on this. Could you please help a fellow beginner with writing a formula? Or do I have to make a custom table inside PowerBi?

Thanks in advance,

Zenons.

[mahoneypat · Accepted solution]

Please see this video on how to do this with the TOPN function.

https://www.youtube.com/watch?v=wKfPS1yZfE0

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

According to my understanding, you want to dynamically display topN data, right?

Here are my data sample:

You could use the following formula :

dynamicTOPN =
VAR rankMeasure =
    RANKX ( ALL ( Sort ), CALCULATE ( MAX ( 'Sort'[Profit ] ) ) )
RETURN
IF ( SELECTEDVALUE ( forN[N] ) >= rankMeasure, 1, 0 )

My visualization looks like this after applying filter:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[Anonymous]

@v-eqin-msft What do I do if my Profit Column is a calculated column, and I want it to change according to a date slicer?

[Anonymous]

Thanks for the help @v-eqin-msft. I just have a slight issue - my profit by client is not a column, it is a measure. What can I do to fix that? 

[harshnathani]

Hi @Anonymous ,

Change your var rankmeasure  to the 

var rankmeasure  = RANKX(ALL (SALES) , [Total Profit])\

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

@harshnathani At first it worked, but then I realized that something was wrong. It still does not work because my SALES table is a table with thousands of invoices and DAX ranks the invoices, not the particular clients. What do you suggest?

[Anonymous]

Wow @harshnathani , it was that simple, thanks a lot!

[mahoneypat · Accepted solution]

Please see this video on how to do this with the TOPN function.

https://www.youtube.com/watch?v=wKfPS1yZfE0

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat