Calculate a new metric based on data from another column that needs to be dynamic as it rolls up

[Jeff_from_YYC]

Hi there. This is my first post so if I'm not posting properly please let me know and I'll improve my forum etiquete 🙂

I'm building a model in PowerBi that will help us calculate emissions for our company. I'm pulling in data from many sources and then calculating the emissions using factors that I've uploaded to a table and reference using the "Related" function. Our industy uses "Emissions Intensity" as a KPI. Emissions intensity is "metric tonnes of CO2e emitted" divided by "volume produced". I've tried to add a column that just divides my "Ttl_CO2e" column by my "Vol" column but when i agg it up it sums the intensity metric, when really it should sum the Ttl_CO2e and divide that by the sum of the Vol. I also searched for a solution on here and there was one post where a measure was used by creating two "sum" measures and then dividing those. The issue is I'd like to be able to put it on a graph and filter down levels in the hierarchy and have it pull back the proper calculation for that level of aggregation.

So this is what a subset of my data might look like. Right now i have a divide function in there, but when i graph the data at say .... Jan 2022, it can't be the sum of the two, it needs to be the multiplication of what is being filtered. I hope this makes sense! 🙂

Thanks so much (I'll get better I promise!)

Location	Ttl_CO2e	Vol	Intensity	Date
1	15	100	= divide [ Ttl_CO2e, Vol, 0]	Jan 2022
2	20	50		Jan 2022
1	15	75		Feb 2022
2	10	40		Feb 2022
3	5	30		Feb 2022
4	60	400		Feb 2022

[v-yiruan-msft · Accepted solution]

Hi @Jeff_from_YYC ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can create a measure as below to get the intensity, the value will change dynamically base on the slicer selections:

Emissions Intensity = DIVIDE ( SUM ( 'Table'[Ttl_CO2e] ), SUM ( 'Table'[Vol] ), 0 )

If my understanding is not correct, please share some sample data(exclude sensitive data) and your expected result with backend logic and special examples just as suggested by @lbendlin . Thank you.

Best Regards

[lbendlin]

Welcome to the forums.

"from another column that needs to be dynamic"  - that is not possible. Columns are static.  You probably meant a measure.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post (as you did already) or use one of the file services. Please show the expected outcome.