Ratio using data in two tables

[Jagan_MFilterIT]

Hi, 

My data model looks something like this and I am looking to calculate the return rate %. 

I have a sales table and return table and both are connected to a common date table in my model. I am trying to get the overall return rate and the product level return rate. 

The formula I use to calculate the overall return rate is Count of records in return table / count of records in the sales table &  this works fine. 

But when I try this in a visual with the product as an Axis to get product level return rate,  it is filtering the values from the return table but gets the total count from the sales table. I.e following formula is getting applied (Count of Product A records in return table/count of All records in the sales table) which is wrong. 

What I want is (Count of Product A records in return table/count of Product A records in the sales table)

Can you please help me with this? 

[v-kelly-msft · Accepted solution]

Hi  @Jagan_MFilterIT ,

Create a measure as below:(For example,you set ProductID from sales table as axis)

Measure = 
IF(ISINSCOPE('Sales Table'[Product ID]),
var _countinreturn=CALCULATE(COUNTROWS('Return Table'),FILTER(ALL('Return Table'),'Return Table'[Product ID]=MAX('Sales Table'[Product ID])))
var _countinsales=CALCULATE(COUNTROWS('Sales Table'),FILTER(ALL('Sales Table'),'Sales Table'[Product ID]=MAX('Sales Table'[Product ID])))+0
Return
DIVIDE(_countinreturn,_countinsales),
DIVIDE(CALCULATE(COUNTROWS('Return Table')),CALCULATE(COUNTROWS('Sales Table'))))

 And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[amitchandak]

@Jagan_MFilterIT , You need to have a common date, product, transaction tables. Against that, you can analyze

divide(countrows(Return),countrows(sales) )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Try to be star schema https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

[Jagan_MFilterIT]

Hi @amitchandak , 

Thanks a lot for your response. 

I have a common date table that connects both the tables. Also, I have common Products on both the tables. This is the formula I am using to get the overall return ratio and it's working fine -  divide(countrows(Return),countrows(sales) )

But when I add product as a dimension in my chart it is returning (Countrows(product X in Return) , countrows(sales)). 

But what i want is (Countrows(product X in Return) , countrows(product X in sales)). 

[amitchandak]

@Jagan_MFilterIT , all are measures.

ret = countrows(Return)

sal =countrows(sales)

//only for product having return

only when there is a return = divide( countrows(Return), sumx(filter( values(Product[product]), not(isblank([ret]))), [sal]))