Custom Tooltip not showing correct values

[Anonymous]

Hi,

I have a stacked column chart that shows the Cost of Production split between the specific inputs. 

I wanted to create a custom tooltip that shows the cost for the specific input when the user hovers over the segments of the column. When I use the auto tooltip that PBI provides, it works, is dynamic and the values are correct. However, it is not esthatically pleasing. 

The inputs themselves are columns in a table called " CAD$,T,Ha " and within the chart, I added measures I created (for proper conversation between currencies) that refrence these columns. 

I created a measure for the custom tooltip to have it switch dynamically, 

_SeleInput2 = 
VAR CurrentInput =
SWITCH(
    TRUE(),
    ISINSCOPE('CAD$,T,Ha'[Seed]), [_CurrencySeed],
    ISINSCOPE('CAD$,T,Ha'[Fertiliser]), [_CurrencyFertiliser],
    ISINSCOPE('CAD$,T,Ha'[Pesticides]), [_CurrencyPesticides],
    ISINSCOPE('CAD$,T,Ha'[Machinery]), [_CurrencyMachinery],
    ISINSCOPE('CAD$,T,Ha'[Fuel and Energy]), [_CurrencyFuelAndEnergy],
    ISINSCOPE('CAD$,T,Ha'[Irrigation Water]), [_CurrencyIrrigation],
    ISINSCOPE('CAD$,T,Ha'[Labour]), [_CurrencyLabour],
    ISINSCOPE('CAD$,T,Ha'[Land Rent]), [_Currency Land Rent],
    ISINSCOPE('CAD$,T,Ha'[Property and Utilities]), [_CurrencyProperty and Utilities],
    ISINSCOPE('CAD$,T,Ha'[Administration]), [_CurrencyAdministration],
    ISINSCOPE('CAD$,T,Ha'[Finance]), [_CurrencyFinance],
    ISINSCOPE('CAD$,T,Ha'[Other]), [_CurrencyOther],
    ISINSCOPE('CAD$,T,Ha'[Storage Depreciation]), [_CurrStorDepreci],
    ISINSCOPE('CAD$,T,Ha'[Storage Labour]), [_CurrStorLabour],
    ISINSCOPE('CAD$,T,Ha'[Storage Fuel/Energy]), [_CurrStorFuel/Energy],
    ISINSCOPE('CAD$,T,Ha'[Storage Pesticides]), [_CurrStorPesti],
    ISINSCOPE('CAD$,T,Ha'[Storage Sprout Control]), [_CurrStorSprout],
    ISINSCOPE('CAD$,T,Ha'[Storage Boxes ]), [_CurrStorBoxes],
    ISINSCOPE('CAD$,T,Ha'[Storage Property/Utilities]), [_CurrStorProperty],
    ISINSCOPE('CAD$,T,Ha'[Storage Losses (shrink)]), [_CurrStorShrinkLoss],
    ISINSCOPE('CAD$,T,Ha'[Storage Other]), [_CurrStorOther],
    BLANK()
)
RETURN
CurrentInput

The result is " - - " and it is unclear why it is doing this. 

[Anonymous]

Hi

You can unpivot your table with your legend columns e.g. Fertilizer, seed in Power Query. Then you will get Attribute & Value Column.

Then you can create below measure & drag in custom tooltip.

_SeleInput2 =

VAR CurrentInput =

SWITCH(

    SELECTEDVALUE('Table'[Attribute]),

    "Seed",sum('Table'[Value]) ,

    "Fertilizer", sum('Table'[Value]) ,

    "Pesticides", sum('Table'[Value]) ,

    BLANK()

)

RETURN

CurrentInput

[Anonymous]

I tried using a different measure,

_SeleInput2 = 
VAR CurrentInput =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Fertiliser]))), [_CurrencyFertiliser],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Pesticides]))), [_CurrencyPesticides],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Machinery]))), [_CurrencyMachinery],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Fuel and Energy]))), [_CurrencyFuelAndEnergy],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Seed]))), [_CurrencySeed],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Irrigation Water]))), [_CurrencyIrrigation],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Labour]))), [_CurrencyLabour],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Land Rent]))), [_Currency Land Rent],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Property and Utilities]))), [_CurrencyProperty and Utilities],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Administration]))), [_CurrencyAdministration],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Finance]))), [_CurrencyFinance],
        NOT(ISBLANK(MAX('CAD$,T,Ha'[Other]))), [_CurrencyOther],
        BLANK()
    )
RETURN
CurrentInput

It does return a value, however it return the same value for all the segaments when hovered over. The value it returns is always the first input after the TRUE () statement (in this case fertilizer). It appears that if it finds a value, it does not consider the rest of the lines, which must mean that it does not recognize the filtering properly. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Here I suggest you to create a Legend table with all type you need in chart Legend.

Legend =
DATATABLE (
    "Legend", STRING,
    "Order", INTEGER,
    {
        { "Seed", 1 },
        { "Fertiliser", 2 },
        { "Pesticides", 3 },
        { "Machinery", 4 },
        { "Fuel and Energy", 5 },
        { "Irrigation Water", 6 },
        { "Labour", 7 },
        { "Land Rent", 8 },
        { "Property and Utilities", 9 },
        { "Administration", 10 },
        { "Finance", 11 },
        { "Other", 12 }
    }
)

Then create a measure as below.

MEASURE =
SWITCH (
    SELECTEDVALUE ( Legend[Legend] ),
    "Seed", [_CurrencySeed],
    "Fertiliser", [_CurrencyFertiliser],
    "Pesticides", [_CurrencyPesticides],
    "Machinery", [_CurrencyMachinery],
    "Fuel and Energy", [_CurrencyFuelAndEnergy],
    "Irrigation Water", [_CurrencyIrrigation],
    "Labour", [_CurrencyLabour],
    "Land Rent", [_Currency Land Rent],
    "Property and Utilities", [_CurrencyProperty and Utilities],
    "Administration", [_CurrencyAdministration],
    "Finance", [_CurrencyFinance],
    "Other", [_CurrencyOther],
    BLANK ()
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for your answer!! 

It works!! 

I have been struggeling with this issue for the last week, so thanks a lot! 

If you have the time, could you explain why this works and the other solution does not?