Forum Discussion
Filter table desc with condition
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 ?
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.
4 Replies
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
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
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.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
