Sum of a measure in a column

[fong]

I need some help in the below model. I need to calculate a volume in SQM (which is length x width) and average selling price per sqm. The rows add up fine but I also need a total volume and ASP/SQM for a category of SKU's to be reflected in the visualizations as well. A snapshot and the formula I currently used is below. Will appreciate some help on this. 

ID	Quantity	Sales	ASP per SQM =sales / sum(Quantity)*average(width)*average(length)	Width (m)	Length (m)	Volume (SQM) = sum(Quantity)*average(width)*average(length)
1	14500	$84,903.29	$34.24	0.019	9	2479.5
2	39100	$256,616.62	$34.54	0.019	10	7429
3	16950	$76,578.69	$23.78	0.019	10	3220.5
4	8448	$17,362.31	$21.63	0.019	5	802.56
5	6480	$5,700.18	$23.15	0.019	2	246.24
6	89010	$328,210.15	$19.41	0.019	10	16911.9
7	88150	$565,387.91	$36.89	0.019	9.15	15324.8775
8	173001	$306,680.29	$46.65	0.019	2	6574.038
Correct value	435639	1641439.44	$30.98			52,988.62
PBI showing			$11.09		0	147,960.00

[v-xicai · Accepted solution]

Hi @fong ,

You may create new measure like DAX below to get correct total sum for the [ASP per SQM].

ASP per SQM_New=

var _table = SUMMARIZE(Table1, Table1[ID],"_Value", [ASP per SQM] )

return
IF(HASONEVALUE(Table1[ID]), [ASP per SQM], SUMX(_table,[_Value]))

Best Regards,

Amy 

Community Support Team _ Amy

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

[fong]

Hi @v-xicai ,

Thanks! That DAX formula was great for me to sum up another measure "Volume (SQM) = sum (Quantity) * average(width)*average (length) where the correct total is a simple sum of the column. That was really helpful and a great solution!

But for the ASP per sqm,  I have a problem because the formula does a sum of the column which doesn't make sense conceptually. The ASP per sqm is total $ sales by category divided by Volume (SQM) by category. The correct answer should be $30.98 but I got $240 in the new formula which is the total sum. Sorry if I wasn't clear.  Is there a way to amend this somehow ?

[DataZoe]

Hi @fong ,

Have you tried AVERAGEX? This will take the calculated values for each row and the average instead of recalculating it when it aggregates.

ASP per SQM =sales / sum(Quantity)*average(width)*average(length)

would then be:

ASP per SQM =AVERAGEX(TableName, sales / sum(Quantity)*average(width)*average(length))

[fong]

I've tried that and all the values still look the same.