Forum Discussion
Top 10 in table
In a table, I display the top 10 of the field CLIENT based of the total sum of the field HOURS (I have a filter Top 10 on CLIENT based on HOURS).
If I display, CLIENT and HOURS, it works as intended.
However, if I had a field between them like OFFICE (because CLIENT might have multiple offices), I do not show the really Top 10.
I would like my Top 1 with all his offices and then second and so on.
How do I achieve that?
CLIENT OFFICE HOURS
AAA OFF1 100
BBB OFF2 150
AAA OFF3 200
CLIENT OFFICE HOURS
AAA OFF1 100 --> Top 1 with OFF1 and OFF3
AAA OFF3 200
BBB OFF2 150 --> Top 2...
Thank you.
Hello, you can use the following measure to achieve it:
Client Rank = RANKX(ALLSELECTED('Table'[CLIENT]), CALCULATE(SUM('Table'[HOURS]), ALLEXCEPT('Table', 'Table'[CLIENT])))- You can replace ALLSELECTED by ALL if the ranking should take into account all clients, not only the selected ones.
- Use ALLEXCEPT to remove filtering effect from OFFICE column.
Result:
2 Replies
Zang_MiResolver II
Hello, you can use the following measure to achieve it:
Client Rank = RANKX(ALLSELECTED('Table'[CLIENT]), CALCULATE(SUM('Table'[HOURS]), ALLEXCEPT('Table', 'Table'[CLIENT])))- You can replace ALLSELECTED by ALL if the ranking should take into account all clients, not only the selected ones.
- Use ALLEXCEPT to remove filtering effect from OFFICE column.
Result:
Thank you for your reply.
It works as intented if I put CLIENT_RANKING, CLIENT, and HOURS_TOTAL.
But as soon as I put OFFICE after CLIENT, the RANKING is 1 for all.I have my measure as follow:
HOURS_TOTAL =
SUMX(
FILTER(
REPORT,
REPORT[SOURCE] = "...." &&
(REPORT[ACTIVITY] = "...." ||
REPORT[ACTIVITY] = "...." ||
REPORT[ACTIVITY] = "...")
),
REPORT[HOURS_REPORTED]
)And Ranking:
CLIENT_RANKING =
RANKX(
ALLSELECTED('REPORT'[CLIENT]),
[HOURS_TOTAL],
,
DESC
)