Tooltip with multiple fields (sorting in descending order)

[diederd]

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 

[MFelix · Accepted solution]

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]

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]

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