Forum Discussion
divide between two tables
In that case you need the following measure:
Pallett number = SUMX ( 'Product Table', DIVIDE ( [Despatch Quantity], [Pallet Quantity] ) )
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
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
In that case you need the following measure:
Pallett number = SUMX ( 'Product Table', DIVIDE ( [Despatch Quantity], [Pallet Quantity] ) )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