cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dennes
Frequent Visitor

Create a string tooltip with total

Hi,

 

I'm trying to create a measure to be used as a tooltip in a treemap. The tooltip should show a list of the top 5 sub-items with a total.

 

For example, each tree item in the chart is a state. The tooltip should show the 5 top cities in the state and the total value for each city. 

 

I'm able to show the top 5 cities, but I couldn't build the expression to include the total values.

 

I used the following expression:

 

Principais Partidos = 
var PartidosCount= DISTINCTCOUNT(deputados[siglaPartido])
Return
if (PartidosCount >5;
CONCATENATEX(TOPN(5;TOPN(5;Summarize(deputados;deputados[siglaPartido];"Total";Count(deputados[id]));[Total];DESC));[siglaPartido];", ";[Total];DESC) & " e mais";
CONCATENATEX(Summarize(deputados;deputados[siglaPartido];"Total";Count(deputados[id]));deputados[siglaPartido];", "))

Captura de Tela (49).png

 

 I would like the result to also contain the total of each item, like this: PSDB (#), PT(#), PRB (#), PR (#), DEM (#)

 

How can I achieve this?

 

Thank you!

 

 

 

 

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft


@Dennes wrote:

Hi,

 

I'm trying to create a measure to be used as a tooltip in a treemap. The tooltip should show a list of the top 5 sub-items with a total.

 

For example, each tree item in the chart is a state. The tooltip should show the 5 top cities in the state and the total value for each city. 

 

I'm able to show the top 5 cities, but I couldn't build the expression to include the total values.

 

I used the following expression:

 

Principais Partidos = 
var PartidosCount= DISTINCTCOUNT(deputados[siglaPartido])
Return
if (PartidosCount >5;
CONCATENATEX(TOPN(5;TOPN(5;Summarize(deputados;deputados[siglaPartido];"Total";Count(deputados[id]));[Total];DESC));[siglaPartido];", ";[Total];DESC) & " e mais";
CONCATENATEX(Summarize(deputados;deputados[siglaPartido];"Total";Count(deputados[id]));deputados[siglaPartido];", "))

Captura de Tela (49).png

 

 I would like the result to also contain the total of each item, like this: PSDB (#), PT(#), PRB (#), PR (#), DEM (#)

 

How can I achieve this?

 

Thank you!

 

 

 

 


@Dennes

See my attached demo.

Measure =
VAR summizedTable =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[city],
        "total sales", SUM ( 'Table'[sales] )
    )
VAR Top3 =
    TOPN ( 3, summizedTable, [total sales], DESC )
RETURN
    CONCATENATEX (
        Top3,
        'Table'[city] & CONCATENATE ( "(", [total sales] )
            & ")",
        ","
    )

Capture.PNG

 

View solution in original post

1 REPLY 1
Eric_Zhang
Microsoft
Microsoft


@Dennes wrote:

Hi,

 

I'm trying to create a measure to be used as a tooltip in a treemap. The tooltip should show a list of the top 5 sub-items with a total.

 

For example, each tree item in the chart is a state. The tooltip should show the 5 top cities in the state and the total value for each city. 

 

I'm able to show the top 5 cities, but I couldn't build the expression to include the total values.

 

I used the following expression:

 

Principais Partidos = 
var PartidosCount= DISTINCTCOUNT(deputados[siglaPartido])
Return
if (PartidosCount >5;
CONCATENATEX(TOPN(5;TOPN(5;Summarize(deputados;deputados[siglaPartido];"Total";Count(deputados[id]));[Total];DESC));[siglaPartido];", ";[Total];DESC) & " e mais";
CONCATENATEX(Summarize(deputados;deputados[siglaPartido];"Total";Count(deputados[id]));deputados[siglaPartido];", "))

Captura de Tela (49).png

 

 I would like the result to also contain the total of each item, like this: PSDB (#), PT(#), PRB (#), PR (#), DEM (#)

 

How can I achieve this?

 

Thank you!

 

 

 

 


@Dennes

See my attached demo.

Measure =
VAR summizedTable =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[city],
        "total sales", SUM ( 'Table'[sales] )
    )
VAR Top3 =
    TOPN ( 3, summizedTable, [total sales], DESC )
RETURN
    CONCATENATEX (
        Top3,
        'Table'[city] & CONCATENATE ( "(", [total sales] )
            & ")",
        ","
    )

Capture.PNG

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors