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

[LewisSW]

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!

[Praful_Potphode · Accepted solution]

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

1. Create a new page in your report

2. Format as tooltip page:

   • Go to Page Settings → Canvas Settings
   • Set Type = "Tooltip"
   • Set appropriate size (e.g., 320 x 240)

3. 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"

4. Add the field parameter slicer to the tooltip page

   • This ensures the tooltip respects the KPI selection

---

Step 6: Configure Tooltip on Main Visual

1. Go to your main Shop bar chart
2. In the Format pane → Tooltip
3. Set Type = "Report page"
4. 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

[V-yubandi-msft]

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.

[V-yubandi-msft]

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.

[V-yubandi-msft]

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.

[Gabry]

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.