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:
