divide between two tables

[Anonymous]

Hello Community,

I would appreciate your help with the below.

I want to understand how I can divide the "Despatch"[Quantity] by the "Pallet"[Pallet_Quantity].

I have built a relationship between them, based on [Product] columns.

I tried SUM(Product[Quantity])/SUM(Pallet[Pallet_Quantity]) but it doesn't work.

Despatch
Product	Quantity	Destination
Product1	150	LocationA
Product1	225	LocationB
Product1	75	LocationB
Product2	100	LocationC
Product2	50	LocationD
Product3	75	LocationA
Product3	25	LocationC
Product3	50	LocationB
Product3	100	LocationD

Pallet
Product	Pallet_Quantity
Product1	25
Product2	50
Product3	25

Thank you,

George

[Anonymous]

Hello @mh2587 ,

Thank you for your reply. Please see the data model below.

I tried to remove the relationship, it didnt work.

Below oyu can see the different results between the manual and the DAX calculations.

Manual Calculation

	Product1	Product2	Product3
	150	100	75
	225	50	25
	75		50
			100
QtySum	450	150	250
PalletQty	25	50	25
PalletNum	18	3	10

DAX calculation

Row Labels	PalletNum
Product1	4.5
Product2	1.5
Product3	2.5
Grand Total	8.5

Thank you,

George

[PaulDBrown]

Create the model as follows. The Dim Product table must have unique product values (if the product table has unique product values you can use it as the dimension table instead of creating a new one):

Create the measures:

Despatch Quantity = SUM('Despatch Table'[Quantity])

Pallet Quantity = SUM('Product Table'[Pallet_Quantity])

Despacth Qty by Pallett Qty = DIVIDE([Despatch Quantity], [Pallet Quantity])

Create the table with the field from the Dim Product table and add the measures

[Anonymous]

Hello @PaulDBrown ,

Thank you for your reply.

The 'Pallet' table includes unique values for [Product], so there is no need for another table.

ProductPallet_Quantity

Product1	25
Product2	50
Product3	25

Below you can see the different results between the manual and DAX calculations.

Manual calculation shows 31 total number of pallets.

	Product1	Product2	Product3
	150	100	75
	225	50	25
	75		50
			100
QtySum	450	150	250
PalletQty	25	50	25
PalletNum	18	3	10

DAX calculation shows 8.5 total number of pallets, matching to your result as well.

Row Labels	PalletNum
Product1	4.5
Product2	1.5
Product3	2.5
Grand Total	8.5

Having said that, focusing on your results table, if I add the 3 rows of the "Despatch Qty by Pallet Qty" column, it gives me the same result as my manual calculation. 

Do you know why the grand total of your results table is not equal to the sum of the 3 rows?

Kind regards,

George

[PaulDBrown · Accepted solution]

In that case you need the following measure:

Pallett number =
SUMX ( 'Product Table', DIVIDE ( [Despatch Quantity], [Pallet Quantity] ) )

[Anonymous]

Hello @PaulDBrown ,

Thank you for your reply.

I can confirm that this measure provides the correct result.

Can I ask for more feedback related to logic behind the correct measure (why this generates the correct result whereas the previous one not)?

Thank you

George

[PaulDBrown]

Well, DIVIDE does at it says. So the division is within the filter context and over the totals.
SUMX, on the other hand, performs the calculation (in this case the DIVIDE) within the filter context (so row by row) and then sums the result of each calculation to deliver the total. Make sense?

[Anonymous]

Hello @PaulDBrown ,

In case I had a dates table and some other dimension tables, linked to the fact table (despatch table), how should this measure be edited? I am asking because your measure is based on the pallet table (or product table).

Thank you

George

[PaulDBrown]

The table in SUMX should be whatever table is used in the visual to create the filter context of the visual. If more than one table is used in the visual, you will probably need to create an expression along the lines of:
SUMX(SUMMARIZE (FactTable, Dim Table 1 [Column1], Dim Table 2 [Column 2]), ....

[Anonymous]

Hello @PaulDBrown ,

Yes, it does. Thank you for that.

Kind regards,

George

[mh2587]

Can you please upload the screenshot of your model or clear the relation between the tables?