Measure between 2 tables with a many to many relationship

[Khomotjo]

Hello everyone,

I have 2 tables : one containes orders picked and the others contains open orders. I want to create a measure to calculate percentage picked on total orders. 

I created a relationship between the two tables ( many to many on date) and calculated the percentage as :

Percentage _Picked = Calculate( Total_Picked/Total_Orders)

Total_Picked is a measure in Table 1 and Total_Orders is a measure in Table 2.

For some reason the data is not pulling through correctly when I visusalise using date fields from either one of the tables.

I am trying to visualise the percentage picked  for each day.

[Bibiano_Geraldo · Accepted solution]

Hi @Khomotjo ,
You should avoid use the Many-to-Many Relationship which can cause unexpected filtering behavior.

You can achieve that by creating a Calendar Table if not already available, and link this Calendar table to both Orders_Picked and Open_Orders tables using a one-to-many relationship on Date.

Calendar = ADDCOLUMNS(
    CALENDAR(MIN('Orders_Picked'[Date]), MAX('Open_Orders'[Date])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "YYYY-MM"),
    "MonthName", FORMAT([Date], "MMMM"),
    "Day", DAY([Date])
)

Now instead of relying on a direct many-to-many relationship, use the Calendar table as the filtering context:

Percentage_Picked = 
VAR Total_Picked = CALCULATE(
    SUM('Orders_Picked'[Picked_Orders]),
    USERELATIONSHIP('Calendar'[Date], 'Orders_Picked'[Date])
)

VAR Total_Orders = CALCULATE(
    SUM('Open_Orders'[Total_Orders]),
    USERELATIONSHIP('Calendar'[Date], 'Open_Orders'[Date])
)

RETURN
DIVIDE(Total_Picked, Total_Orders, 0)

[Khomotjo]

Thanks @bhanu_gautam , I think the issue is in the relationship. I tried linking the tables on a different field(same data type) but the  results are still the same. Values from one of the tables  are pulling through as constant numbers.

[bhanu_gautam]

Share the relationship or sample data with us

[Khomotjo]

Thanks @bhanu_gautam , sorted. I followed the troubleshooting guide  available online.

[bhanu_gautam]

@Khomotjo Verify that the relationship between the two tables is correctly established on the date field

Then create measures 

Total_Picked = SUM(Table1[PickedOrders])
Total_Orders = SUM(Table2[OpenOrders])

Create a measure to calculate the percentage of picked orders

Percentage_Picked = DIVIDE([Total_Picked], [Total_Orders], 0)