Total of a measure

[ibelouris]

Hello All, I have been reading the forum and searching online without any luck for this issue I am facing.

My table has the following structure:

sample data

TypeDescription	Item	ValuationPrice	ValuationPriceLY	AcquisitionCost	([ValuationPrice]-[ValuationPriceLY])/[ValuationPriceLY]	ROI (previous column*AcquisitionCost)
Merchandise	1	1.46	1.40	9,042.00	0.04	396.88
Merchandise	2	1.46	1.35	405.00	0.08	33.29
Merchandise	3	1.32	1.30	7,065.60	0.02	116.63
Merchandise	4	2.44		109.23	-	-
Merchandise	5	13.91		428.74	-	-
Merchandise	6	9.25		223.54	-	-
Merchandise	7	6.98		248.51	-	-
Merchandise	8	4.70		304.14	-	-
Merchandise	9	2.64	2.42	73.38	0.09	6.44
Merchandise	10	13.87		138.90	-	-
Merchandise	11	9.43	9.03	146.36	0.04	6.48
Merchandise	12	7.53	6.89	180.12	0.09	16.75
Merchandise	13	4.76	4.36	345.92	0.09	32.27

ROI Is a measure that =

IFERROR( ([ValuationPrice]-[ValuationPriceLY])/[ValuationPriceLY]*SUMx(Procurement_Periodics,Procurement_Periodics[AcquisitionCost]),0)

The excel total is ~608 on the measure total I am getting ~16.131

The picture above highlights the issue.

 

ROI is calculated correctly for each line.

However, ROI total is not. It should be the sum of the ROI of all the lines that are filtered. The total of ROI is the calucation of the total of cost (here measure)*([ValuationPrice]-[ValuationPriceLY])/[ValuationPriceLY] (here measure2).

Things I have tried so far that give incorrect results:

Measure 4 = sumx(VALUES(Procurement_Periodics[ItemType]),[ROI])

Measure 3 = VAR _table=SUMMARIZE(Procurement_Periodics,Procurement_Periodics[ItemType],"_value",[ROI])

RETURN

IF(HASONEVALUE(Procurement_Periodics[ItemType]),[ROI],SUMX(_table,[_value]))

Does anyone have an idea of how to approach this?

[v-xiaoyan-msft]

Hi @ibelouris ,

Has your problem been solved? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn Yan

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

[ibelouris · Accepted solution]

Turns out this is giving me the total I need.

Measure =

sumx(VALUES(Procurement_Periodics[ItemDescription]),[ROI])