Re: List the Top or Bottom 5 Based on a value

violet11_6 · ‎08-06-2023

Hi,

I have a table with the following fields:

Company Name	Customer Name	Accounts Receivable	Work-in-progress	Unbilled Receivable	Total Exposure	Month
A	Cust_AB	1000	5500	1800	8300	Month1
B	Cust_BC	2000	6500	2800	11300	Month1
C	Cust_CD	3000	7500	3800	14300	Month1
A	Cust_AB	1050	5550	1850	8450	Month2
B	Cust_BC	2050	6550	2850	11450	Month2
C	Cust_CD	3050	7550	3850	14450	Month2

.

I have a card that flags out the month-on-month difference of the various categories eg:

M-o-m variance on Accounts Receivable = +$150

of which I need to list the Top or Bottom 5 [Company Name] contributors to the variance, depending on whether the varianceis a positive or negative value. I also need to ignore the zero m-o-m variances. Within the top or bottom 5 [Company Name], I also need to list the top/bottom 5 [Customer name] contributing to the m-o-m variance.

Any suggestion on how I can amend the RankX formula to achieve all of the above?

Thanks!

Anil_kapkoti · ‎08-07-2023

Here is a systematic step-by-step guide to achieve your goal:

Step 1: Create a Table Visual
- Begin by creating a table visual in your chosen power bi, based on your detailed requirements. This table will serve as the starting point for your analysis.

Step 2: Create Required Measures
- Identify the measures you need to calculate and display in your table visual. These measures should represent the metrics or calculations you want to see as the output in the table.

Step 3: Write Final Measure for Card Visual
- Once you have all the measures you need, write a final measure that you want to display in a card visual. This measure will be the primary value you want to emphasize on the card.

Step 4: Create Rank Conditional Measure
- Write a rank conditional measure based on your final measure. This measure will help rank the data in your table visual based on the values of the final measure.

Step 5: Create a Bookmark
- Use your reporting tool's bookmark feature to capture the current state of the report, including the table visual with the rank measure applied. This will save the sorting and filtering settings for the visual.

Step 6: Add Transparency Button
- Insert a transparency button over the card visual that you want to make clickable. This button will be used to apply actions when clicked.

Step 7: Set Up Bookmark Action
- Configure the transparency button to have an action that is triggered when clicked. The action should be set to apply the bookmark you created in Step 5.

Step 8: Populate Account Names
- With the help of the rank measure created in Step 4 and the bookmark action, when the card visual is clicked, it will navigate to the bookmarked table visual with the desired rank applied. This will populate the account names, showing the top 5 or bottom 5 based on the rank measure.

Step 9: Test and Refine
- Test the functionality of the transparency button and the bookmark action to ensure everything is working as expected. Refine the design and settings as needed to achieve the desired outcome.

By following these systematic steps, you can create an interactive report that allows users to click on a card visual, apply the desired action, and populate the account names based on the ranking measure. This will provide an intuitive and user-friendly experience for exploring the data in the report.

violet11_6 · ‎08-07-2023

Hi,

Do you mean set it at the filter? That does not work as I need the list to automatically detect whether to pick from the top or from the bottom. Any suggestion?