Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## How will I get top ranking Root cause for each product?

Hi,

I have a table structure like below:

 Product Name Root cause Total  No Product Name 1 Root cause 1 12 Product Name 1 Root cause 2 15 Product Name 1 Root cause 3 20 Product Name 1 Root cause 4 35 Product Name 2 Root cause 6 12 Product Name 2 Root cause 6 15 Product Name 2 Root cause 7 20 Product Name 2 Root cause 8 39 Product Name 3 Root cause 6 12 Product Name 3 Root cause 10 15 Product Name 3 Root cause 71 20 Product Name 3 Root cause 81 40

Now, Can i built a table like below where I can get top ranking Root cause for each product?

 Product Name Root cause Total  No Product Name 1 Root cause 1 35 Product Name 2 Root cause 8 39 Product Name 3 Root cause 81 40

Thanks,

Sandip

1 ACCEPTED SOLUTION
Super User

Hi @dsandip ,

Add the following measure:

``Ranking = RANKX(ALLSELECTED('Table'[Product Name],'Table'[Root cause],'Table'[Total  No]), 'Table'[Total  No],SUM('Table'[Total  No]),DESC)``

Now filter the visual on the top 3:

You can also try the following code:

``````Value =
CALCULATE (
SUM ( 'Table'[Total  No] ),
KEEPFILTERS (
WINDOW (
1,
ABS,
3,
ABS,
SUMMARIZE (
ALL ( 'Table' ),
'Table'[Product Name],
'Table'[Root cause],
'Table'[Total  No]
),
ORDERBY ( 'Table'[Total  No], DESC )
)
)
)``````

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

5 REPLIES 5
Community Support

Hi @dsandip

You can create two measures as follow.

``Rootcause = CALCULATE(MAX([Root cause]), ALLEXCEPT('Table', 'Table'[Product Name]))``
``Total No = CALCULATE(MAX([Total  No]), FILTER(ALLEXCEPT('Table', 'Table'[Product Name]), [Root cause] = [Rootcause]))``

Is this the result you expect?

Best Regards,
Yulia Xu

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

Super User

Hi @dsandip ,

Add the following measure:

``Ranking = RANKX(ALLSELECTED('Table'[Product Name],'Table'[Root cause],'Table'[Total  No]), 'Table'[Total  No],SUM('Table'[Total  No]),DESC)``

Now filter the visual on the top 3:

You can also try the following code:

``````Value =
CALCULATE (
SUM ( 'Table'[Total  No] ),
KEEPFILTERS (
WINDOW (
1,
ABS,
3,
ABS,
SUMMARIZE (
ALL ( 'Table' ),
'Table'[Product Name],
'Table'[Root cause],
'Table'[Total  No]
),
ORDERBY ( 'Table'[Total  No], DESC )
)
)
)``````

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

Hi,

I have used the below DAX and created the measure:

```Value =
CALCULATE (
SUM ( 'Table'[Total  No] ),
KEEPFILTERS (
WINDOW (
1,
ABS,
3,
ABS,
SUMMARIZE (
ALL ( 'Table' ),
'Table'[Product Name],
'Table'[Root cause],
'Table'[Total  No]
),
ORDERBY ( 'Table'[Total  No], DESC )
)
)
)```

and it works for me.

Frequent Visitor

Hi,

it is not that i will always have 3 rows and can filter results by it. I can have multiple products and each product may vae multiple root case. So, I have created below dax to create a table like below:

Complaints by Product by Complaint Type =
var vTable = Filter(
SUMMARIZE(
filter('FACT_COMPLAINT', FACT_COMPLAINT[DIM_OPS_CNDCT_ZONE_KEY] = "AESA")
,'TDIM_PRODUCT'[PRODUCT_NAME]
,FACT_COMPLAINT[NATURE_OF_COMPLAINT]
,"Complaint NO", [Rolling 12 Months Complaints]
),
[Complaint NO] >0 && [PRODUCT_NAME] <> BLANK() && [NATURE_OF_COMPLAINT] <> BLANK()
)
RETURN
vTable
the above table produce the below result:

Now, for each product I have to find out the top nature of complaints or root case , so that it can always show only 1 row for each product for top root cause name like below:

from the set of rows like below:

So, top root cause name by product for each row it should show.

How to write dax for that?

Super User

Hi @dsandip ,

Use the following measure instead.

``````Value =
CALCULATE (
SUM ( 'Table'[Total  No] ),
KEEPFILTERS (
WINDOW (
1,
ABS,
1,
ABS,
SUMMARIZE (
ALL ( 'Table' ),
'Table'[Product Name],
'Table'[Root cause],
'Table'[Total  No]
),ORDERBY('Table'[Total  No], DESC),
,PARTITIONBY('Table'[Product Name])
)
)
)``````

The partition by name gives you the additional value you need.

I added a new product to the table and as you can see it returns 4 rows

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors