cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jagan_MFilterIT
Frequent Visitor

Ratio using data in two tables

Jagan_MFilterIT_0-1615789812354.png

 

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? 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1615966875071.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1615966875071.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

amitchandak
Super User
Super User

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!