Divide a column by sum of another column

[Anonymous]

Using the following formula for a calculated column:

I am expecting to get 1200/44 = $27.3 for both rows.

Total Freight and Admin column is a simple column taken straigh from a dataset (no calculation there).

Quantity column is summing per transaction.

Any suggestion? @parry2k 

[MFelix]

Hi @Anonymous ,

Try the following measure:

measure = SUM(TABLE[Total Freigth and Admin]) / CALCULATE([Quantity] ; ALL(Table[Transaction ID]))

Regards,
MFelix

[TomMartens]

Hey,

my DAX column is looking like this, basically it's quite similar to the DAX statement you provided:

Column = 
var theQuantity = SUM('Table2'[quantity])
return
DIVIDE('Table2'[freight],theQuantity)

and my sample data looks like this, also similar to yours, but with your expected result:

So there might be a difference. can you please explain what you mean by "Quantity column is summing by transaction".
If quantity is a column then SUM('tablename'[quantity] will calculate across all rows of the table as there is no filtercontext present.

Regards,
Tom

[Anonymous]

Hello @ TomMartens I tried the formula but it didn't change the result.

"Quantity column is summing by transaction" - each of those two transactions has multiple lines (13 and 31= 44 as quantity).

By summing I mean if I leave the field as is (Don't summarize) it would show 1 in the column of quantityf for both transactions. Below is the screenshot of it.

[TomMartens]

Hey,

can you please provide a pbix file, either your original one, or one that contains sample data but enables us to recreate the issue you are facing. Upload the file to onedrive or dropbox and share the link.

Regards,
Tom

[Anonymous]

I uploaded the simplified file to dropbox. Here is the link @TomMartens 

https://www.dropbox.com/s/so6h2h0xz192ngg/Demo.pbix?dl=0