Forum Discussion

game1's avatar
game1
Helper III
1 year ago
Solved

Division between tables

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 meas...
DAX
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
quantumudit
Super User
1 year ago

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's avatar
game1
Helper III
1 year ago

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's avatar
    quantumudit
    Super User
    1 year ago

    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