How to get the amount of returned products

[Anonymous]

Hi,

I'm trying to create a visualization where I'm able to show how many orders were returned for each year. I made the relationship between my Orders and Returns table, and I also created a Calendar that's connected to my Order date column.

Basically, what I want to achieve is if I have this in my Orders table:

And I have this in my Returns table:

I want to get the following result:

How should I do this?
I'm sending my sample file if it is any help: https://fastupload.io/en/pCmUoJN7HxWSvzK/file

Thank you for the help.

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

According to your description,  you want to show how many orders were returned for each year.Right?

Here are steps you can refer to:

(1)I import your sample file in the Power BI Desktop and i create a 'Date' table .Then i create the relationship between tables like you said.

Date = CALENDAR( FIRSTDATE('Orders'[Order Date]),LASTDATE( 'Orders'[Order Date]))

(2)We can create a measure : [Amount of returns]

Amount of returns = COUNTROWS( FILTER('Orders' , 'Orders'[Order ID] in VALUES('Returns'[Order ID])))

(3)We can put the 'Date'[Year] and [Amount of returns] in the Table visuals. Then we can meet your need , the result is as follows :

If this method does not meet your needs, you can provide us with the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

[tamerj1]

Hi @Anonymous 

please try

Amount of Return =
COUNTROWS ( FILTER ( Orders, Orders[Order ID] IN VALUES ( Return[Order ID] ) ) )

[tackytechtom]

Hi @Anonymous ,

In the table, I chose the year column from Order Date. Note "År" is year in Swedish...

This is what I did:

Then create a measure:

CountReturnedOrders = CALCULATE ( DISTINCTCOUNT ( 'order'[Order ID]),  returns[Returned] = "Yes" )

Then use it in a graph like this:

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Greg_Deckler]

@Anonymous Maybe make your Order to Return relationship Both direction?