Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello there,
I am struglling to getting top 25 unique customers when filter the table using Alert_Id. Basically, I have these columns in table which you can find in csv file. The goal is to show top 25 unique customers based on highest value. The Item can be repeated but name has to be unique. I have tried so many different things but nothing seems to be working as expected because of Multiple customers have used multiple items and hence I am getting duplicate rows. The table has to be dymic because whenever user filters the table using Alert_id it should return those top unique customers that associated with Alert_id(Alert_id is a single selection). So whenever user select their Alert_id that table should display their data. I have tried below measure,
First I created calculated column to break the tie for price because many Item shares the same price:
Here is expected output if I select Alert_ID = 123 from filter and it can be different when I select different Alert_ID.
FYI: I have tried topn with max price and even with RANKX but no luck. I always endedup having multiple customers.
Solved! Go to Solution.
I was able to figure out how to get unique values. Here is the solution that worked for me.
First, I created calculated column with my price column:
sum value = Table[PRICE] + RAND()
Then, I have created one measure that calculates the rank:
rank with table =
RANKX(
CALCULATETABLE(
VALUES('Table'[ITEM]),
ALLSELECTED('Table'[ITEM])
),
CALCULATE(SUM(Table[sum value])),
,DESC,
Dense
)
Then I applied the filter on NAME column to get top 25 based on sum value calculated column. Also, dragged my measure on filters pane and applied the filter where Rank with table = 1.
That's how I got unique names with highest valued ITEM.
I was able to figure out how to get unique values. Here is the solution that worked for me.
First, I created calculated column with my price column:
sum value = Table[PRICE] + RAND()
Then, I have created one measure that calculates the rank:
rank with table =
RANKX(
CALCULATETABLE(
VALUES('Table'[ITEM]),
ALLSELECTED('Table'[ITEM])
),
CALCULATE(SUM(Table[sum value])),
,DESC,
Dense
)
Then I applied the filter on NAME column to get top 25 based on sum value calculated column. Also, dragged my measure on filters pane and applied the filter where Rank with table = 1.
That's how I got unique names with highest valued ITEM.
Hi,
I am not sure how much i can help but i would like to try. Share data in a format the can be pasted in an MS Excel file.
Hello Ashish,
NAME ITEM DATE ITEM_COUNT PRICE
PMC ITEM_1 2022-06-06 3 7636.5
A&A ITEM_2 2022-05-02 3 6228
JKL ITEM_2 2022-07-19 3 6228
JKL ITEM_3 2022-07-19 3 5827.41
A&A ITEM_3 2022-05-02 3 5827.41
JKL ITEM_1 2022-07-19 3 4581
A&A ITEM_1 2022-05-02 3 4581
PLUM ITEM_1 2022-04-08 2 4072.5
A&A ITEM_5 2022-05-02 3 3622.5
JKL ITEM_5 2022-07-19 3 3622.5
NAME | ITEM | DATE | ITEM_COUNT | PRICE |
PMC | ITEM_1 | 2022-06-06 | 3 | 7636.5 |
A&A | ITEM_2 | 2022-05-02 | 3 | 6228 |
JKL | ITEM_2 | 2022-07-19 | 3 | 6228 |
JKL | ITEM_3 | 2022-07-19 | 3 | 5827.41 |
A&A | ITEM_3 | 2022-05-02 | 3 | 5827.41 |
JKL | ITEM_1 | 2022-07-19 | 3 | 4581 |
A&A | ITEM_1 | 2022-05-02 | 3 | 4581 |
PLUM | ITEM_1 | 2022-04-08 | 2 | 4072.5 |
A&A | ITEM_5 | 2022-05-02 | 3 | 3622.5 |
JKL | ITEM_5 | 2022-07-19 | 3 | 3622.5 |
Could you show the expected result please?
Yes, the second screenshot is expected result.
Hi,
Despite my best effort, i just cannot understand your question. You mention about filtering by a certain Alert_ID when infact in the table that you have shared there is no Alert_ID at all. May be someone else who understands your question can help.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
153 | |
125 | |
114 | |
111 | |
95 |