Calculation between columns on a table

[bolabuga]

Good day.

i have a Table with 1.700.000 rows for the columns "econres", "categoria", "consume".

Categoria column has 5 values (R,I,P,C,M).

I need to do: sum "econres" if "categoria" = "R" and sum "consume" if "categoria" = "R", then divide the resulted sum of "consume" and "econres", because i need the value (consume per econres on the categoria "R")

Thks

[KHorseman · Accepted solution]

Two ways to do this. First, if you only need this for categoria "R" then you can explicitly write the measure to only use that:

consume per econres categoria R = CALCULATE(
	DIVIDE(
		SUM(TableName[consume]),
		SUM(TableName[econres])
	),
	FILTER(
		TableName,
		TableName[categoria] = "R"
	)
)

The second method would apply if you also want to do the same calculation for categorias I, P, C, and M. In that case you can simply write the basic form of the above measure:

consume per econres = DIVIDE(
	SUM(TableName[consume]),
	SUM(TableName[econres])
)

...and plot that measure against the categoria column (that is, use categoria as the rows in a matrix, or the legend or X axis in some chart). The context from that column will make the measure give you each categoria, behaving just like the filter statement in my first example.

[KHorseman · Accepted solution]

Two ways to do this. First, if you only need this for categoria "R" then you can explicitly write the measure to only use that:

consume per econres categoria R = CALCULATE(
	DIVIDE(
		SUM(TableName[consume]),
		SUM(TableName[econres])
	),
	FILTER(
		TableName,
		TableName[categoria] = "R"
	)
)

The second method would apply if you also want to do the same calculation for categorias I, P, C, and M. In that case you can simply write the basic form of the above measure:

consume per econres = DIVIDE(
	SUM(TableName[consume]),
	SUM(TableName[econres])
)

...and plot that measure against the categoria column (that is, use categoria as the rows in a matrix, or the legend or X axis in some chart). The context from that column will make the measure give you each categoria, behaving just like the filter statement in my first example.

[Anonymous]

Thanks! This is exactly what I needed!

My final based on your sample is here:

Measure2 = CALCULATE(DIVIDE(SUMX(Table1,[measure1]*[Column1]),[measure1]), FILTER(Table1, Table1[Column1] <> 999))

[bolabuga]

would it be possible to add more than 1 filter in the "calculate" command??

consume per econres categoria R = CALCULATE(
	DIVIDE(
		SUM(TableName[consume]),
		SUM(TableName[econres])
	),
	FILTER(
		TableName,
		TableName[categoria] = "R"
	)
)

[KHorseman · Accepted solution]

@bolabuga you can have more than one filter and more than one condition per filter.

If you want to filter based on more than one column in the same table:

consume per econres categoria R = CALCULATE(
	DIVIDE(
		SUM(TableName[consume]),
		SUM(TableName[econres])
	),
	FILTER(
		TableName,
		TableName[categoria] = "R" &&
		TableName[OtherColumn] = "X" 
	)
)

 && is "and", meaning both conditions must be met. Substitute that with || if you want to do an "or" condition. You can string together as many conditions as you like this way.

If you have two tables connected by a relationship, and you want to filter based on conditions in both tables:

consume per econres categoria R = CALCULATE(
	DIVIDE(
		SUM(TableName[consume]),
		SUM(TableName[econres])
	),
	FILTER(
		TableName,
		TableName[categoria] = "R" &&
		TableName[OtherColumn] = "X" 
	),
	FILTER(
		OtherTable,
		OtherTable[ColumnName] = "Y"
	)
)

[bolabuga]

Nice Explanation, really thks KHorseman.

[bolabuga]

Thks KHorseman, took me sometime to unsderstand i have the value i want repeated all across the 1.7kk rows and i can just throw it on my graphic and choose to show "average" instead of "sum".

obs: i cant say for sure the option correct translation is average, for me its showing in portuguese "media"