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.

Reply
dsandip
Frequent Visitor

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

Hi,

I have a table structure like below:

Product NameRoot causeTotal  No
Product Name 1Root cause 112
Product Name 1Root cause 215
Product Name 1Root cause 320
Product Name 1Root cause 435
Product Name 2Root cause 612
Product Name 2Root cause 615
Product Name 2Root cause 720
Product Name 2Root cause 839
Product Name 3Root cause 612
Product Name 3Root cause 1015
Product Name 3Root cause 7120
Product Name 3Root cause 8140

 

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

Product NameRoot causeTotal  No
Product Name 1Root cause 135
Product Name 2Root cause 839
Product Name 3Root cause 8140

 

Thanks,

Sandip

1 ACCEPTED SOLUTION
MFelix
Super User
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:

MFelix_0-1707489069522.png

 

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 )
        )
    )
)

MFelix_0-1707490244647.png

 


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



View solution in original post

5 REPLIES 5
v-xuxinyi-msft
Community Support
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]))

 

vxuxinyimsft_0-1707724856238.png

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.

MFelix
Super User
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:

MFelix_0-1707489069522.png

 

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 )
        )
    )
)

MFelix_0-1707490244647.png

 


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



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.

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:
dsandip_0-1707491140525.png

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:

dsandip_2-1707491321037.png

from the set of rows like below: 

dsandip_1-1707491283386.png

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

How to write dax for that?

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

MFelix_0-1707492568788.png

 


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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.