Calculate function as a substitute for SUMIF/SUMIFS

[Anonymous]

Dear experts, running into some problems when trying to create a set of formulas to calculate ratios at the row level of an output table. In excel I would do this using a SumIF or SumIFs formula. I understand I should be able to do this using a Calculate expresssion.

CALCULTATE (SUM(TABLE[COLUMN]), TABLE[COLUMN])

In this formula the second table column would be the criteria range. What Im trying to do is get a % of the Component KG for each "RM" in the first column. I have maybe 1,000 unique RM values so the total sum of the column im trying to create should add up to 100,000% or 1000 as a numerical value.

It seems as if the column I need to use (Component KG) for my % calculation cant be used in my calculate function since its not part of the underlying table. Is there any way around this? Spent a good 3 hours trying different things and I cant replicate the simple aggregated multiplicative results in the Component_KG column using a calculated column instead of calculated measure.

[jthomson]

The second half of your calculate function doesn't make any sense - try calculating component_kg as a column rather than a measure, then make another column dividing the sum by the sum of the whole table, but filtered for where the RM value matches what it is in the current row

[Anonymous]

---

@jthomson wrote:

The second half of your calculate function doesn't make any sense - try calculating component_kg as a column rather than a measure, then make another column dividing the sum by the sum of the whole table, but filtered for where the RM value matches what it is in the current row

---

Thats what I initially was trying to do but component kg is calcuated as the average input ratio * the average output quantity KG per manufucaturing batch. When I try to calculate as a column instead of calculation I dont get the right answer anymore in my summary table. In the below table the component KG is the correct value as a calculation. Comp KG 1 & 2 are my attempts at the column calculation.