cancel
Showing results for 
Search instead for 
Did you mean: 

Concatenated list of top items

Frequent Visitor
1540 Views
Highlighted
Dennes Frequent Visitor
Frequent Visitor

Concatenated list of top items

NAME:

Concatenated list of top items

 

DESCRIPTION:

Concatenates a list of top X items based on the sum of a measure. It's good for use as a tooltip for several visuals

 

(This measure is similar to the existing 'Concatenated list of values', but this measure uses the sum of a measure to select the top X items)

 

PARAMETERS:

Name: Concatenate Field

Tooltip: The field that will be concatenated

Type: Categorical field

 

Name: Data Field

Tooltip: The field that will be used to define the top X items to concatenate

Type: Numeric Field/Measure

 

Name: Number of Values

Tooltip: If there are more than this number of values, 'more...' will be added to the end of the string

Type: Integers

 

 

DAX:

Top {Concatenate Field} by {Data Field}=

var qty= DISTINCTCOUNT({Concatenate Field})
Return
if (qty >{Number of Values};
     CONCATENATEX(

        TOPN({Number of Values};

           TOPN({Number of Values};

                ADDCOLUMNS (VALUES ( {Concatenate Field} );
                           "Total"; CALCULATE ( SUM ( {Data Field} ) );

           [Total];DESC)

         );

     {Concatenate Field} & "(" & Format([Total]/1000000;"Currency") & "M)";", ";[Total];DESC)

     & " e mais";
     CONCATENATEX(

          ADDCOLUMNS (VALUES ( {Concatenate Field} );
                           "Total"; CALCULATE ( SUM ( {Data Field} ) );

     {Concatenate Field} & "(" & Format([Total]/1000000;"Currency") & "M)";", ")

)

 

Example:

 

I used the WorldWideImportersDW sample database to build a very small report illustrating this measure. The PBIX file is in attach.

 

The image below shows the measure being used as a tooltip for the visual, I highlighted the measure:

 

QuickMeasure.png