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 IV
Super User IV

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

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.