Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Custom Tooltip not showing correct values

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's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      Anonymous
      Not applicable

      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's avatar
        Anonymous
        Not applicable

        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? 



  • Anonymous's avatar
    Anonymous
    Not applicable

    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