Filter table desc with condition

[hylosko]

Hello !

I want to filter my table by profit desc and show only values where total sum of dev_num = 30

i mean for example:

Dev_num  |   Profit

10                 100000

10                  59000

5                    34000

5                    25000

my table looks like this:

any ideas for this measure ?

[Anonymous]

Hi @hylosko ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[Anonymous · Accepted solution]

Hi @hylosko ,

Did you try to apply TopN filters to the visualization like:

But as my data sample shown, there may be several same values , so the Top 3 have 4 rows. In this case, if you just want to display 3 rows, you may rank by Profit firstly and then rank by a column with unique value like project_name.

Assume there are less than 100 rows:

Flag = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[PROFIT])),,DESC,Dense) *100 + RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[PROJECT_NAME])),,DESC,Dense)

Measure = RANKX(ALLSELECTED('Table'),[Flag],,ASC,Dense)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hylosko]

Hi @PhilipTreacy ,

I mean to show only 30 devs sorted by highest profit every time. if it will be more than 30 devs, calculated table shouldnt show other devs groups.

In table it will looks like this:

DEV_NUM  |    PROFIT

10                   1000000

10                       40000

5                           3000

5                           2000

That i should get from my whole table, as you can see total number of dev is always 30,  and profit is always sorted desc by highest value

[PhilipTreacy]

Hi @hylosko 

Your requirements/logic are not clear.  There are multiple ways you could sum Dev_Num to get 30.

How to know which values to use?

Also, this is the Power Query forum.  PQ doesn't use Measures.  If you want a measure written in DAX you should post in the Desktop or DAX forums.

regards

Phil