Sometimes it can be valuable to simply show what the top performing products or customers are. The TopN filter in Power BI makes this an easy task but is bounded to a visual. To get the best performing category in a single measure, this Quick calculation can be used.
In the pbix, use the relative date slicer to see the top sold product changing.
How it works
Per category the selected measure is calculated, the TopN part will only select the first row based on the descending order for the selected measure over the categories. When 2 rows in the categories share the same value, the lastnonblank selects the latest value based on the column sorting order. This can be changed to firstnonblank to select the first.
Examples:
Top 1 Productname for Sales =
CALCULATE (
LASTNONBLANK ( 'Product'[Productname]; 1 );
FILTER ( TOPN ( 1; VALUES ( 'Product'[Productname] ); [Sales]; DESC ); TRUE () )
)
To get the value for the top 1 category, replace the LASTNONBLANK() with the {Base value}
Sales for Top 1 productname =
CALCULATE (
[Sales];
FILTER ( TOPN ( 1; VALUES ( 'Product'[Productname] ); [Sales]; DESC ); TRUE () )
)
To get the friendly label simply use:
Best product label =
"The best sold product is " & [Top 1 Productname for Sales] & " with " & FORMAT([Sales for Top 1 productname];"€ #,#") &" in sales"
NAME:
Top 1 per category
DESCRIPTION:
Calculates the top 1 of the base value over the category
PARAMETERS:
Name: Base value
Tooltip: The value that is used to determine the top 1
Type: Numerical field / measure
Name: Category
Tooltip: The category in which you want to calculate the top 1
Type: Categorical field
DAX:
Top 1 {Category} for {Base value} =
CALCULATE (
LASTNONBLANK ( {Category}; 1 );
FILTER ( TOPN ( 1; VALUES ( {Category} ); {Base value}; DESC ); TRUE () )
)
eyJrIjoiN2RmOWQ3MzctNDIwMC00N2U3LTk3ZjUtMTM3M2ZhYTI0N2NkIiwidCI6IjY2MjU3MWNmLWM5ZjgtNGQyOS1hYzcxLTA2N2E0NTY4MDExZCIsImMiOjh9
