topic Re: Dynamic Top 5/Bottom 5 Visuals Using Field Parameters & Measures in DAX Commands and Tips

Dynamic Top 5/Bottom 5 Visuals Using Field Parameters & Measures

LewisSW — Wed, 29 Oct 2025 12:21:01 GMT

Hi all,

I'm trying to create a P&L report where users can switch between 3 KPIs (Sales, Gross Profit, Cash Contribution) using a field parameter. I have separate measures for each KPI that calculates the TY vs. LY delta:

_Total_CashContribution_Delta = VAR _KPI = "Cash Contribution" VAR TY = CALCULATE([Sum of GBP Amount BFX], 'D-SHOP_REPORTING_SORT'[Reporting Row Name] = _KPI) * -1 VAR LY = CALCULATE( [Sum of GBP Amount BFX], 'D-SHOP_REPORTING_SORT'[Reporting Row Name] = _KPI, FILTER( ALL('D-FISCAL_YEAR'), 'D-FISCAL_YEAR'[Fiscal Year] = 'D-SELECTED_BASE_YEAR'[Selected Base Year Measure Fiscal Year] ) ) * -1 RETURN TY - LY

I created a field parameter _KPI_Row like this:

_KPI_Row = { ("Sales", NAMEOF('_MEASURES'[_Total_Sales_Delta]), 0), ("Gross Profit", NAMEOF('_MEASURES'[_Total_GrossProfit_Delta]), 1), ("Cash Contribution", NAMEOF('_MEASURES'[_Total_CashContribution_Delta]), 2) }

I've created a Shop bar chart that uses _KPI_Row as a slicer to switch between sales, GP, cc deltas.

I want to create a report tooltip so that when a user hovers over a shop, it shows Top 5 and Bottom 5 GL accounts (separate bar charts) that drive the selected KPI delta for that shop. For example, if Shop 5 has the highest Sales delta, the tooltip would show which GL accounts contributed the most or least to that delta.

I’m not sure how to make the tooltip visuals dynamic based on both the selected KPI (via the field parameter) and the selected shop.

Any guidance or examples would be greatly appreciated!

Re: Dynamic Top 5/Bottom 5 Visuals Using Field Parameters & Measures

Gabry — Wed, 29 Oct 2025 16:14:05 GMT

Hey there,

you’ll want to create a new page and set it up as a tooltip. Then, add a bar chart and use the field parameter for the data you want to display. To ensure consistency, sync a slicer on the new page with the slicer on your main page, so the same measure is selected in the tooltip bar chart. Lastly, configure the bar chart to display only the top and bottom 5 items.

Re: Dynamic Top 5/Bottom 5 Visuals Using Field Parameters & Measures

V-yubandi-msft — Thu, 30 Oct 2025 09:11:08 GMT

Hi LewisSW ,

The suggestion from Gabry , is spot on and should help you achieve the dynamic tooltip behavior you need.

Additionally, if you plan to display both the Top 5 and Bottom 5 GL accounts, you might want to use a RANKX-based measure to handle those visuals. This allows you to filter for the top 5 and bottom 5 ranks based on the selected KPI delta.
Helpful reference: RANKX function (DAX) - DAX | Microsoft Learn

Thanks for your prompt reply, Gabry

Regards,
Yugandhar.

Re: Dynamic Top 5/Bottom 5 Visuals Using Field Parameters & Measures

V-yubandi-msft — Mon, 03 Nov 2025 05:13:37 GMT

Hi LewisSW ,
May I know if your issue has been resolved, or do you still need any additional details. Please let us know if you require any further information.

Thank You.

Re: Dynamic Top 5/Bottom 5 Visuals Using Field Parameters & Measures

V-yubandi-msft — Thu, 06 Nov 2025 10:11:53 GMT

Hi LewisSW ,
Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

Re: Dynamic Top 5/Bottom 5 Visuals Using Field Parameters & Measures

Praful_Potphode — Fri, 07 Nov 2025 18:46:34 GMT

Hi LewisSW

You need to create measures that dynamically respond to the field parameter selection and filter to the correct GL accounts for the hovered shop.

Step 1: Create a Dynamic Delta Measure

This measure will automatically pick the correct KPI based on the field parameter selection:

_Dynamic_Delta = 
VAR SelectedKPI = SELECTEDVALUE('_KPI_Row'[_KPI_Row])

VAR Result = 
    SWITCH(
        SelectedKPI,
        "Sales", [_Total_Sales_Delta],
        "Gross Profit", [_Total_GrossProfit_Delta],
        "Cash Contribution", [_Total_CashContribution_Delta],
        BLANK()
    )

RETURN Result

Step 2: Create GL Account Level Delta Measures

Create similar delta measures but at the GL Account level:

_GLAccount_Sales_Delta =
VAR _KPI = "Sales"
VAR TY = CALCULATE([Sum of GBP Amount BFX], 'D-SHOP_REPORTING_SORT'[Reporting Row Name] = _KPI) * -1
VAR LY = CALCULATE(
    [Sum of GBP Amount BFX],
    'D-SHOP_REPORTING_SORT'[Reporting Row Name] = _KPI,
    FILTER(
        ALL('D-FISCAL_YEAR'),
        'D-FISCAL_YEAR'[Fiscal Year] = 'D-SELECTED_BASE_YEAR'[Selected Base Year Measure Fiscal Year]
    )
) * -1
RETURN TY - LY

_GLAccount_GrossProfit_Delta =
VAR _KPI = "Gross Profit"
VAR TY = CALCULATE([Sum of GBP Amount BFX], 'D-SHOP_REPORTING_SORT'[Reporting Row Name] = _KPI) * -1
VAR LY = CALCULATE(
    [Sum of GBP Amount BFX],
    'D-SHOP_REPORTING_SORT'[Reporting Row Name] = _KPI,
    FILTER(
        ALL('D-FISCAL_YEAR'),
        'D-FISCAL_YEAR'[Fiscal Year] = 'D-SELECTED_BASE_YEAR'[Selected Base Year Measure Fiscal Year]
    )
) * -1
RETURN TY - LY

_GLAccount_CashContribution_Delta =
VAR _KPI = "Cash Contribution"
VAR TY = CALCULATE([Sum of GBP Amount BFX], 'D-SHOP_REPORTING_SORT'[Reporting Row Name] = _KPI) * -1
VAR LY = CALCULATE(
    [Sum of GBP Amount BFX],
    'D-SHOP_REPORTING_SORT'[Reporting Row Name] = _KPI,
    FILTER(
        ALL('D-FISCAL_YEAR'),
        'D-FISCAL_YEAR'[Fiscal Year] = 'D-SELECTED_BASE_YEAR'[Selected Base Year Measure Fiscal Year]
    )
) * -1
RETURN TY - LY

Step 3: Create Dynamic GL Account Delta Measure

_Dynamic_GLAccount_Delta = 
VAR SelectedKPI = SELECTEDVALUE('_KPI_Row'[_KPI_Row])

VAR Result = 
    SWITCH(
        SelectedKPI,
        "Sales", [_GLAccount_Sales_Delta],
        "Gross Profit", [_GLAccount_GrossProfit_Delta],
        "Cash Contribution", [_GLAccount_CashContribution_Delta],
        BLANK()
    )

RETURN Result

Step 4: Create Top 5 and Bottom 5 Measures

Top 5 GL Accounts:

_Top5_GLAccount_Delta = 
VAR CurrentGLAccount = SELECTEDVALUE('GLAccountTable'[GL Account])
VAR SelectedKPI = SELECTEDVALUE('_KPI_Row'[_KPI_Row])

VAR Top5Table = 
    TOPN(
        5,
        SUMMARIZE(
            'GLAccountTable',
            'GLAccountTable'[GL Account],
            "Delta", [_Dynamic_GLAccount_Delta]
        ),
        [Delta],
        DESC
    )

VAR IsInTop5 = CurrentGLAccount IN VALUES(Top5Table[GL Account])

RETURN
IF(IsInTop5, [_Dynamic_GLAccount_Delta], BLANK())

Bottom 5 GL Accounts:

_Bottom5_GLAccount_Delta = 
VAR CurrentGLAccount = SELECTEDVALUE('GLAccountTable'[GL Account])
VAR SelectedKPI = SELECTEDVALUE('_KPI_Row'[_KPI_Row])

VAR Bottom5Table = 
    TOPN(
        5,
        SUMMARIZE(
            'GLAccountTable',
            'GLAccountTable'[GL Account],
            "Delta", [_Dynamic_GLAccount_Delta]
        ),
        [Delta],
        ASC
    )

VAR IsInBottom5 = CurrentGLAccount IN VALUES(Bottom5Table[GL Account])

RETURN
IF(IsInBottom5, [_Dynamic_GLAccount_Delta], BLANK())

Step 5: Create the Tooltip Page

Create a new page in your report
Format as tooltip page:
- Go to Page Settings → Canvas Settings
- Set Type = "Tooltip"
- Set appropriate size (e.g., 320 x 240)
Add two bar charts:
Top 5 Chart:
- Axis: GL Account
- Values: [_Top5_GLAccount_Delta]
- Title: "Top 5 Contributing GL Accounts"
Bottom 5 Chart:
- Axis: GL Account
- Values: [_Bottom5_GLAccount_Delta]
- Title: "Bottom 5 Contributing GL Accounts"
Add the field parameter slicer to the tooltip page
- This ensures the tooltip respects the KPI selection

Step 6: Configure Tooltip on Main Visual

Go to your main Shop bar chart
In the Format pane → Tooltip
Set Type = "Report page"
Select your tooltip page

Please try above steps in sequence and let me know if it works for you.

if it doesnt work for you please provide sample pbix.

please give kudos or mark it as solution once confirmed.

Thanks and regards,

Praful