topic Top 1 per category in Quick Measures Gallery

Top 1 per category

Anonymous — Fri, 04 Aug 2017 10:54:25 GMT

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

Re: Top 1 per category

mdaamirkhan — Thu, 15 Mar 2018 04:32:15 GMT

Hello

How will I defined two product name in the following wuery that you are given.

Re: Top 1 per category

Anonymous — Fri, 16 Mar 2018 08:51:35 GMT

Hi mdaamirkhan

What do you want to accomplish? The names of the 2 products that have the highest sales?

Because it's a measure you cannot return 2 rows. You should either contatenate the rows or use a table visual to filter on the top 2 based on sales. You could also create a New table with DAX where you can use the below FILTER() statement to reutn the top 2 products.

To concatenate the top 2 products you can use the formula below.

Top2Concatenated =
CONCATENATEX (
    TOPN ( 2; VALUES ( 'Product'[Productname] ); [Sales]; DESC );
    'Product'[Productname];
    ". "
)

Re: Top 1 per category

srirambalan — Wed, 15 Jul 2020 13:10:07 GMT

HI, I found this very useful - considering that i am an amateur, i am struggling to replicate the same for finding the TOP VALUE in the Subcategory, under the category determined as TOP category.

e.g. if the BASE VALUE is Total Sale - Sum(Sale Value)

Brand A - $15000

- Cat A - $11000

SubCat 1 - $3000

SubCat 2 - $2000

SubCat 3 - $6000

- Cat B - $4000

Subcat 1 - $2500

Subcat 3 - $1500

Let's assume Brand A is the TOP 1 basis the Sale Value in Brands, then:

- under BRAND A, CAT A is the TOP 1 category value
- Under Cat A, SubCat 3 is the TOP 1 Sub category value

How can i achieve this, so that i can view this in a matrix or table visual ? Note that the table visual would list the following
- Customer ID

- Total Sale Value of the customer

- Customer TOP Brand

- Total Sale value of the TOP Brand

- Customer TOP Category

- Total Sale Value of the TOP category
- Customer TOP Sub Category
- Total Sale Value of the TOP category

I have a single fact table which features all the above.