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

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