topic Re: Division between tables in DAX Commands and Tips

Division between tables

game1 — Thu, 05 Sep 2024 06:11:57 GMT

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]

Re: Division between tables

quantumudit — Thu, 05 Sep 2024 06:16:22 GMT

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

Re: Division between tables

game1 — Thu, 05 Sep 2024 12:36:11 GMT

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

Re: Division between tables

quantumudit — Thu, 05 Sep 2024 13:48:47 GMT

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.

Re: Division between tables

Anonymous — Fri, 06 Sep 2024 09:46:17 GMT

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.