cancel
Showing results for
Did you mean:

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.

Frequent Visitor

## How to create an "Others" value with Top 3 products under each category with a filter on the page.

I first uploaded my query table named "Sheet1" as follows:

I wanted to create a DAX expression which showed the Top 3 and Others values, so I first created a table named "PRODUCT NAMES" with all the product names along with an "Others" row:

PRODUCT NAMES = UNION(
VALUES(Sheet1[products]),
ROW("Product Names","Others")
)
The table was formed as follows with "Others" being denoted by the red circle:

The Top 3 products were first denoted with the expression:
TopN = 3

The sum of the "Current Volume" is denoted using the formula:
Total Volume = CALCULATE(SUM(Sheet1[Current volume]))

A ranking was then denoted with "Others" at 4th place using the DAX expression:

RankbyVolume = IF(
SELECTEDVALUE('PRODUCT NAMES'[products])="Others",
[TopN]+1,
RANKX(
ALL('PRODUCT NAMES'),
[Total Volume])
)
The formula was used to rank products according to Total Volume" but to determine the value for "Others" I used a DAX expression to add up all values greater than the Top 3 as follows:

top product & others =
IF(
[RankbyVolume]<=[TopN], [Total Volume],
IF(SELECTEDVALUE('PRODUCT NAMES'[products])= "Others",
SUMX(
FILTER(
ALL('PRODUCT NAMES'[products]),
[RankbyVolume]>[TopN]),
[Total Volume]
)
)
)

I then used a Pivot Table visual to display the Total Volume, RankbyVolume and top product & others values as shown below:
I then applied a filter where "top product & others"is greater than 0:
I also added category values from "Sheet1", which has 3 categories of products (alpha, beta & omega), in the row with the following results:
The main problem is that I want a DAX expression which displays 4 rows (Top 3 products and Others ) for each category on "Sheet1" (alpha, beta & omega).

Does anyone have the solution to this problem?
2 REPLIES 2
Community Support

Hi @Dawn7047 ,

Could you please tell me that you just want 4 rows in one catagory?

Top 3 and Others rows?

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Yes, I wanted to know how to display 4 rows (Top 3 products and Others) for each category (alpha, beta and omega) of products.

Apologies for not making it more clear in the question.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors