Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarcinSV
Helper I
Helper I

Divide sum of values in measure by values in second table joining by month and product

Hi to all,

 

I'm newly in PBI and was trying to do my best working on solution but I gave up for this moment.

I have to tables:

 

TABLE A (date|prod|value)

 

2019-01-01 | prod A | 300

2019-02-01 | prod B | 100

 

 TABLE B (prod|year|month|value)

 

Prod A | 2019 | 1 |30

Prod B | 2019 |2  |22

 

Now this values of month (300 and 100) I'd like to use count an average for PROD_NAME by MONTH, i.e.:

 

for PRODUCT PROD A, month 1 = 300 / 30 = 10 AVG

for PRODUCT PROD B,  month 2 = 100 / 22 = 4,54 AVG

  

I would be greatfull for any help and tips.

All the best!

1 ACCEPTED SOLUTION

Hi @MarcinSV,

 

I would suggest you create two tables. Then everything will be easy and clear. Please download the demo from the attachment.

Divide-sum-of-values-in-measure-by-values-in-second-table-joining-by-month-and-product

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
MarcinSV
Helper I
Helper I

Does anyone has an idea how to solve it?

I will ask in different way (without product), maybe then I will find by myself the rest solution.

 

How to divide one value in month (table A) by second value (from different table, B) but exactly for the same month like in table A?

For example:

Table A: in December 2018 value = 300,

Table B: in Decemeber 2018 value = 30

So 300 / 30 = 10

 

Now I have one measure: Average of working day = SUM(Table A [Sales]) / 22 -> and I need to change this 22 to dynamic, depending on month /year value from table A and put in this place value from the same month/year but from table B. Now I have no idea how to change it using DAX that value from A will "choose" value from B...

Hi @MarcinSV,

 

I would suggest you create two tables. Then everything will be easy and clear. Please download the demo from the attachment.

Divide-sum-of-values-in-measure-by-values-in-second-table-joining-by-month-and-product

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much, now it works great!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.