cancel
Showing results for
Did you mean: Frequent Visitor

## Ratio using data in two tables 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)

1 ACCEPTED SOLUTION  Community Support

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

4 REPLIES 4  Community Support

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

Proud to be a Super User! Frequent Visitor

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

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

Proud to be a Super User! Announcements #### 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. #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### 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. Top Solution Authors
Top Kudoed Authors
Users online (4,136)