Forum Discussion

diederd's avatar
diederd
Helper II
6 years ago
Solved

Tooltip with multiple fields (sorting in descending order)

Hello Power BI Community

 

I'm having some trouble in being able to generate a tooltip with multiple fields (with values summed and sorted in descending order). I have included a data sample to assist with the explanation:

 

- two tables (tblBank and tblStats)

- linked by means of a transaction code

- company will be bought by multiple banks

- needing to produce a report as follows

 

Company Name                Date                      Banks

------------------------------------------------------------

Company X                       Dec-18                   Bank 4, Bank 2, Bank 1

 

Tooltip to provide fee paid per bank, ie. Bank 4 - 400

 

I'm hoping this makes sense, but I'm pulling my hair trying to get this resolve.

 

Thank you in advance.

 

Sample.xlsx 

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi diederd ,

     

    Create 3 dimension tables:

    • Calendar
    • Transaction
    • Companies

    This tables will have to have unique values and make the one to many connection between the other two tables.

     

    Now setup the following measure:

    Banks = CONCATENATEX(tblBank;tblBank[Bank];",")

    This will give you the setup for the bank information on a single row.

     

    Create a table with the following setup:

    • Companies[Company]
    • DimDate[MonthYear]
    • [Banks]

     

    Now create a tooltip page and add a table visual with the following setup:

    • tblBank[Bank]
    • tblBank[Fee paid (LCY)]

     

    Turn on the tooltip on the first table and setup the previous tooltip page on that see result below and on PBIX file attach.

     

    You can set up the tooltip with other type of information not only a table and can be use on all other visuals.

     

    Regards,

    MFelix

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi diederd ,

     

    Create 3 dimension tables:

    • Calendar
    • Transaction
    • Companies

    This tables will have to have unique values and make the one to many connection between the other two tables.

     

    Now setup the following measure:

    Banks = CONCATENATEX(tblBank;tblBank[Bank];",")

    This will give you the setup for the bank information on a single row.

     

    Create a table with the following setup:

    • Companies[Company]
    • DimDate[MonthYear]
    • [Banks]

     

    Now create a tooltip page and add a table visual with the following setup:

    • tblBank[Bank]
    • tblBank[Fee paid (LCY)]

     

    Turn on the tooltip on the first table and setup the previous tooltip page on that see result below and on PBIX file attach.

     

    You can set up the tooltip with other type of information not only a table and can be use on all other visuals.

     

    Regards,

    MFelix

    • diederd's avatar
      diederd
      Helper II
      6 years ago

      MFelix.

       

      Thank you for your prompt response and providing the much needed guidance to resolve my conundrum. Could I however ask as to how you went about getting the distinct table for your Transaction Table?

       

      I'm working through your example but unable to fathom how you did it..

       

      Thank you again for your help on resolving.

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi  diederd ,

         

        I have made a single step with all the links but you can the following approach on a blank query:

         

        • Add a blank step with the syntax = tblBank[Transaction Code]
        • Add a blank step with the syntax = tblStats[Transaction Code]
        • Now do an append between both previous steps
        • Remove duplicates

        Check the code below:

         

        let
    Source = tblBank[Transaction code],
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Custom1 = tblStats[Transaction code],
    #"Converted to Table1" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Appended Query" = Table.Combine({#"Converted to Table", #"Converted to Table1"}),
    #"Removed Duplicates" = Table.Distinct(#"Appended Query")
in
    #"Removed Duplicates"

         

        Regards,

        MFelix