Division between tables

[game1]

Hii,
I want to divide 2 measures that are in 2 different tables. The division must be for each same month of the same year. For example, we want to divide for the month January 2020 (date1) the measure1 by the measure2 of the month of January 2020 (date2).
So we have:
Table1[measure1]
Table2[measure2]
Table1[date1]
Table2[date2]

[Anonymous · Accepted solution]

I would like to thank quantumudit for the valuable input.

Hi @game1 ,

Here I will add some details by creating simple data to achieve the desired results.
I have taken the following steps:

1.Create simple data.

Table1:

Table2:

2.Create a calendar table:

DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2024,1,1), DATE(2024,10,1)),
    "YearMonth", FORMAT([Date], "YYYY-MM")
)

3.Create relationships as quantumudit  describes:

4.Create three measures:

Measure1 = COUNT('Table1'[ClothesID])
Measure2 = SUM('Table2'[Sales])
divide = DIVIDE('Table2'[Measure2],'Table1'[Measure1])

5.Use the DateTable's "YearMonth" as a slicer field,and the result is as follows:

Model relationships determine how the data carries out interactions between different tables, and should also follow the principles of the star schema when we model, here are a couple of links that will help you:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[quantumudit]

Hello @game1 

Could you please provide more details about the problem you're encountering, along with a sample dataset and the expected outcome? This information will be invaluable in comprehending your specific needs.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[game1]

The division should only be done between the same month of the same year. For example, we should only divide the total number of clothes in the month of January 2010 by the Purchase amount in the month of January 2010.

Table1[measure1] -- COUNT the total number of clothes 
Table2[measure2] -- SUM of amount of the Purchase
Table1[date1] -- Date of arrival
Table2[date2] -- Date of purchase

[quantumudit]

Hello @game1 

It appears you're encountering issues with data modelling. Here's a solution:

- Establish a separate date table.

- Form a one-to-many relationship between the new date table and Table-1.

- Form a one-to-many relationship between the new date table and Table-2.

- Utilize the dates from the new date table for calculations instead of using dates from the individual tables.

Implementing these steps should resolve your problems. If the issue persists, please provide a small sample of an anonymized dataset along with the expected outcome (either a snapshot or a data table) for further assistance.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree