Forum Discussion

Ayreon69's avatar
Ayreon69
Regular Visitor
2 years ago
Solved

How to divide two columns from different matrix

Hello,

 

I looked for this answer but didn't find it despite a lot of topics already existing.

 

I am sure it's really easy but I need help please, I would like to divide those values : 

Divide the value on this matrix

By this one : 

 

I was sure it was easy so I tried this measure : 

Measure = DIVIDE(SUM('Pretsta 2023'[compteur]),SUM('besa 2023'[expo]))

 

And got this : 


While I am expecting this : 

 

Thanks for your help

 

Need Help
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Hi Ayreon69,

    As you said, it is easy to do this, and the key is to use a separate calendar table (dimension table) which has one to many relationships with the two fact tables in your data model.  

     

    In order to make the data in a most easily consumable format for dax, I unpivoted the two fact tables in your screen print above (by data inputting first and) by clicking unpivot other columns ribbon in Power Query before loading the data into the data model. 

    Then, I created a separate calendar table, using dax formula like below:

    Then, I created the relationships between the calendar dimension table and the two fact tables as shown below:

    Simply dividing the sum of the values of the Table1 by that of Table2 will generate your expected output as shown below:

     

    I attach the link to the pbix file below:

    Annee_Couverture.pbix

     

3 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    2 years ago

    Hi Ayreon69,

    As you said, it is easy to do this, and the key is to use a separate calendar table (dimension table) which has one to many relationships with the two fact tables in your data model.  

     

    In order to make the data in a most easily consumable format for dax, I unpivoted the two fact tables in your screen print above (by data inputting first and) by clicking unpivot other columns ribbon in Power Query before loading the data into the data model. 

    Then, I created a separate calendar table, using dax formula like below:

    Then, I created the relationships between the calendar dimension table and the two fact tables as shown below:

    Simply dividing the sum of the values of the Table1 by that of Table2 will generate your expected output as shown below:

     

    I attach the link to the pbix file below:

    Annee_Couverture.pbix