Trouble counting occurence of values between 3 connected columns.

[Anonymous]

Hello all, 

I am having tremendous difficulty in getting around a Power BI function. 

I am super new to Power BI and I've learned a lot already but I'm still confused about some topics. 

I uploaded some data from excel into my Power BI applicaiton and it had some data as such. 

Returns (Tab 1)

Order ID (column 1)

1

2

3

Orders (Tab 2)

Order ID (column1)          Product ID (column 2)

1                                       100 

1                                       101

2                                       101

3                                       101 

3                                       100

4                                       100 

Product (tab 3)

Product ID (column 1)           Product(column 1)

100                                         Table 

101                                         Chair

What I am trying to do is figure out the top 5 products that are returned. 

I know the data above only has 2 products, but the actual data in my Power BI file has many more. 

I basically want to take the Order ID from tab 1, compare them with the multiple product ID's each order has in tab 2 and then compare that with the product names in tab 3 to figure out the top 5 products that are returned. 

I've already establihed all the relationships and linkages. 

I've tried to use the "Top N", count distinct and sum functions but I was unable to get the filteration mechanism to work properly.

I kept on getting the total number. I also tried using matrix but that didn't work either. 

You can access the file with this link in case anyone wants to look at the data I am working with to get a better idea of my problem. Page 3 is where majority of my attempts have been at trying to solve the problem.

I'm basically trying to find the top 5 most returned products, ideally in a graph and maybe using a slicer.

https://1drv.ms/u/s!AnMQvyG3G5w6iTcKZUaBvA_oLalv 

That link above leads to the download page to downloads the PBIX file I am working with. 

I would greatly appreciate any help. 

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your needs, I did a test, created a column to get the number of each product ID, and created a virtual table of products and quantities. Refer to the following. Then you can create topn to get the first few values.

count_Pro =
CALCULATE (
    COUNTROWS ( Orders ),
    FILTER (
        ALL ( Orders ),
        Orders[Order ID] = EARLIER ( Orders[Order ID] )
            && Orders[Product ID] = EARLIER ( Orders[Product ID] )
    )
)

Table = SUMMARIZE(Orders,Orders[Order ID],Orders[Product ID],Orders[count_Pro])

Best Regards,
Henry

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

Below is the sample pbix file.

[Anonymous]

This file seems to be right, but needed a list of product names by # of times they were returned. Is there a formula I can add to make it like that from the orginal file?

I think the file you posted doesn't have the names. 

[v-henryk-mstf]

Hi @Anonymous ,

Can you describe it in more detail.

Best Regards,
Henry

[Vera_33]

Hi @Anonymous 

When you look at Product Name, do you differentiate Product Number as well? 

I am using a ranking to filter the top returned products

Count of Return = CALCULATE(COUNTROWS(Orders),TREATAS(VALUES(Returns[Return Number]),Orders[Order ID]))

ranking = RANKX(ALL(Products),[Count of Return],,DESC,Dense)