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!

[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.