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

[dsandip]

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

[v-xuxinyi-msft]

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.

[MFelix · Accepted solution]

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

[dsandip]

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.

[dsandip]

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?

[MFelix]

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