Calculate rate from two related tables

[dsabo]

Hi,

I'm having trouble calculating a rate from a relationship between two tables.

Suppose that I have two tables

Table A

Year | Week | User | Qty | ID

2017     5        joe    26    1 

2017     5        jane  28    2

Table B

Year | Week | User | Date | Qty | TableAID

2017    5        joe     01/12    10  1

2017    5        joe     02/12    11  1

2017    5        joe     03/12    13  1

2017    5        jane   01/12    9    2

2017    5        jane   02/12    8    2

2017    5        jane   03/12    5    2

I need a measure SUM([Table B].Qty) / SUM([Table A].Qty) 

I tried creating a new column with the related values of Table A in Table B but when I create the measure it results in agregating the Table A for each Date on Table B.

This is the result that I'm looking forward

Result

Year | Week | User | Date | Qty | Table A.Qty | Rate (Table B.Qty/Table A.Qty)

2017    5        joe     01/12    10       26                   0.38

2017    5        joe     02/12    11       26                   0.42     

2017    5        joe     03/12    13       26                   0.50

2017    5        jane   01/12    9         28                   0.32

2017    5        jane   02/12    8         28                   0.28

2017    5        jane   03/12    5         28                   0.18

Then I would like to group only by Year and Week as follows using the SAME measure

Year | Week | Date | Qty | Table A.Qty | Rate (Table B.Qty/Table A.Qty)

2017    5        01/12    19       54                   0.35

2017    5        02/12    19       54                   0.35     

2017    5        03/12    18       54                   0.33

Then I would also like to be able to group only by Year as follows

Year | Date | Qty | Table A.Qty | Rate (Table B.Qty/Table A.Qty)

2017   01/12    19       54                   0.35

2017   02/12    19       54                   0.35     

2017   03/12    18       54                   0.33

How should I create the Measure Rate???????

Thanks!

[v-shex-msft]

Hi @dsabo,

If you want the column group effect on measure, you can try to use below measure:

Percent = SUM('Table B'[Qty])/SUM('Table A'[Qty])

Regards,

Xiaoxin Sheng

[dsabo]

Hi @v-shex-msft

Here is the thing, I gave you a bad example thats why you could replicate my issue

Lets add a few more rows on Table A (week 6)

If we group by week we have the follow QtyA per week

If whe use your formula to calculate without grouping it works perfect

Percent = SUM('Table B'[Qty])/SUM('Table A'[Qty])

But if I group per Year, Week and Date the sum of QtyA is getting the sum of all records, and not the sum of Week 5 and Week 6.

I need that the percent is calculated based on QTYA of the specific YEAR and WEEK so I expect the following

Thanks!

[v-shex-msft]

Hi @dsabo,

Can you share us a sample pbix file to test?

Regards,

Xiaoxin Sheng

[dsabo]

Hi @v-shex-msft

Sure! Here

Thanks!

[richbenmintz · Accepted solution]

You should do 4 things, 

1. Create a date table and relate it to both of your fact tables

2. Create a User table and relate it to both of your fact tables

3. Create simple measures that sum up your facts

4. Create a measure that derives the percent using the divide dax function with your two new measures.

You will then use the User Name and date values in your grid with will filter your measures in your two tables based on the relationships.

The beauty of the star schema. 

[Phil_Seamark]

Hi @dsabo

Here are some things you can try....

First, you could create the following two calculated columns on Table B

Table A.Qty = RELATED('Table A'[Qty])

Rate = DIVIDE('Table B'[Qty],'Table B'[Table A.Qty])

This should make your table look like this..

Then I found the following three measures could help with the next parts

Qty Measure = SUM('Table B'[Qty])

Qty Measure ALL = CALCULATE(SUM('Table B'[Qty]),ALL('Table B'))

Rate Measure = DIVIDE('Table B'[Qty Measure],'Table B'[Qty Measure ALL])

Which allowed be to create the following grid visuals

May need some tweaking when fitting to your real data but could be close.

[dsabo]

Hey @phil,

Thanks for your reply.

I agree with the first part, it works when you don't group in any way 

Table A.Qty = RELATED('Table A'[Qty])

Rate = DIVIDE('Table B'[Qty],'Table B'[Table A.Qty])

I need the same Rate measure when grouping by year, week or whatever.

The example that you created does not calculate the rate that you created in the first example. You are just calculating the % based on the total of the column and not based on the value of Table A.Qty as you did in the first example.

I need as Table B [Qty] / Table B[Table A.Qty] and the measure should work when grouping by year, week, or user.

Thanks!