Calculation based on text field category

[MelB_EIA]

Good evening everyone

Really hoping for some help... I am trying to calculate a figure (CO2 amount) which is dependant on the category of a text filter (type of gas) and a measure (total volume of gas), and which has multiple options/values depending on the text filter.

CO2 = ( [Total volume gas seized (kg)]/1000) *

SWITCH (

Datalist[type of gas],

"Bromochloromethane", 6900,

"CFC-11", 5560,

"CFC-112", 4620,

"CFC-113", 6520,

"CFC-12", 11200,

0) )

It doesn't seem to like the fact I'm using a text filter and says it cannot find the name, even though it exists. Any ideas? Is it because it's not a numerical field? Is there another DAX expression I can use that will do the same thing?

Thanks in advance!

[Syk]

If this is a calculated column, you could create an additional calculated column to look at type of gas and use it to multiply instead of using switch.

[MelB_EIA]

Thanks for your response. The list of gas types is quite long, so I tried putting it in a separate table so I could create a new calculated column to do a lookup, which worked, but it is the next part I'm stuck on as it doesn't seem to recognise the calculated column in the DAX when I create a measure.

to clarify, I need to create a new field (calculated column or measure?) that calculates the CO2 using this formula:

( [Total volume gas seized]/1000) * GWP

The GWP values are in a separate table and are dependant on 'Type of gas' field.

Total volume gas seized is a measure.

Example of main incident data, with incident ID:

ID	Date	Type of gas	Reported volume	Estimated volume	Total volume
1	01/02/2022	CFC-11	12000.00	0.00	12000.00
2	02/02/2022	HCFC-502	0.00	58000.00	58000.00
2	02/02/2022	HFC-23	800.00	0.00	800.00
3	03/03/2022	HCFC-406A	542.00	0.00	542.00
4	04/02/2022	CFC-11	0.00	8795.00	8795.00
5	05/02/2022	HFC-23	0.00	7845.00	7845.00
5	05/02/2022	HFC-507	0.00	5274.00	5274.00

Example of GWP value table:

Type of gas	GWP
Bromochloromethane	6900
CFC-11	5560
CFC-112	4620
CFC-113	6520
CFC-12	11200
HCFC-502	4657
Halon 1211	1930

There are also multiple gas types seized per incident which are joined together by ID (it is a relational database). 

Any ideas? Thanks!

[Anonymous]

Hi @MelB_EIA,

You can take a look at the following measure formula to get the result based on the current gas type and id group:

Measure =
SUMX (
    SUMMARIZE (
        T1,
        [Type of gas],
        [ID],
        "total",
            SUM ( T1[Total volume] ) / 1000
                * LOOKUPVALUE ( T2[GWP], T2[Type of gas], [Type of gas] )
    ),
    [total]
)

Regards,

Xiaoxin Sheng

[MelB_EIA]

Hi Xiaoxin

Thank you for your response. I have tried this measure formula but I receive an error: " The SUM function only accepts a column reference as an argument".

"Total volume" is a calculated column - would this cause this?

Thanks

[Anonymous]

Hi @MelB_EIA,

The calculated column should not affect the formula calculations, I suppose they may be related to the [Type of gas] field, you can try to use the following measure formulas if helps:

Measure =
SUMX (
    SUMMARIZE (
        T1,
        [Type of gas],
        [ID],
        "total",
            VAR currGas =
                SELECTEDVALUE ( T1[Type of gas] )
            RETURN
                SUM ( T1[Total volume] ) / 1000
                    * LOOKUPVALUE ( T2[GWP], T2[Type of gas], currGas )
    ),
    [total]
)

If the above does not help, can you please share a pbix file with some dummy data to test?

Regards,

Xiaoxin Sheng