Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
bolabuga
Helper V
Helper V

Calculation between columns on a table

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

2 ACCEPTED SOLUTIONS
KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@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"
	)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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))

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"
	)
)

@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"
	)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nice Explanation, really thks KHorseman.

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"

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors