Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Concatenated list of top items
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)
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
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)";", ")
)
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:
eyJrIjoiZWVlYjg3NjYtMGJjYS00ZTFmLWE4ZDUtNmMyMTk0YjdkYzQxIiwidCI6IjQ3YTY0Y2UzLTNlYmItNGIxYS05MjBjLTU3NWY5Yjg5MjA2OSJ9