Hi @Poojajunnu , you’re asking for dynamic context control in Power BI: when all months are selected, show the Top 5 opportunities by Gross Margin (normal behavior); but when a single month is selected, freeze that same Top 5 set — i.e., show their monthly data, but not re-rank them.
That’s a subtle but powerful trick because Power BI re-applies filter context every time you slice, so the “Top 5” naturally changes. To lock that Top 5 while still allowing other visuals (like fiscal months) to update, we’ll need a DAX measure that remembers which opportunities were top 5 in the “all months” context.
Step-by-step setup
Step 1. Define your base measure
Gross Margin = SUM('YourTable'[Gross Margin])
Step 2. Create a measure to identify Top 5 Opportunities (in all-month context)
We use ALLSELECTED() to evaluate all fiscal months regardless of current slicer selection.
Top 5 Opp Flag =
VAR Top5Opps =
TOPN(
5,
ADDCOLUMNS(
VALUES('YourTable'[Opportunity]),
"@GM", [Gross Margin]
),
[@GM], DESC
)
RETURN
IF(
'YourTable'[Opportunity] IN Top5Opps,
1,
0
)This measure returns 1 for those opportunities that were in the Top 5 when all months were visible in the slicer.
Step 3. Create your display measure for Gross Margin
Now create a measure that filters based on that flag:
Gross Margin (Top 5) =
CALCULATE(
[Gross Margin],
KEEPFILTERS(
FILTER(
ALL('YourTable'[Opportunity]),
[Top 5 Opp Flag] = 1
)
)
)This ensures that regardless of what fiscal month is selected, your table only includes the same 5 opportunities.
Step 4. Build your table visual
Rows: Account, Opportunity
Values: Gross Margin (Top 5)
Slicer: Fiscal Period (with both columns)
Now when:
You select all months → The Top 5 are calculated normally.
You select a single month → The same Top 5 (from the “all months” state) are displayed, showing their data for that period.
Optional: Make it dynamically aware
If you want the behavior to depend only on whether all months or a single month are selected, we can add logic to toggle it automatically:
Gross Margin (Top 5 Dynamic) =
VAR MonthCount = DISTINCTCOUNT('Fiscal'[Fiscal Month])
VAR IsSingleMonth = MonthCount = 1
VAR Top5Opps =
TOPN(
5,
ADDCOLUMNS(
VALUES('YourTable'[Opportunity]),
"@GM", CALCULATE([Gross Margin], ALL('Fiscal'[Fiscal Month]))
),
[@GM], DESC
)
RETURN
CALCULATE(
[Gross Margin],
KEEPFILTERS(
FILTER(
ALL('YourTable'[Opportunity]),
IF(IsSingleMonth, 'YourTable'[Opportunity] IN Top5Opps, TRUE())
)
)
)This version dynamically locks the Top 5 only when a single month is selected — otherwise it behaves normally.
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]
