Calculation based on text field category

Question

Good evening everyone&nbsp;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.&nbsp;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) )&nbsp;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?&nbsp;Thanks in advance!

syk · Answer

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.&nbsp;

melb_eia · Answer

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 · Answer

Hi&nbsp;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 · Answer

Hi Xiaoxin&nbsp;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?&nbsp;Thanks

anonymous · Answer

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

Forum Discussion

Calculation based on text field category

5 Replies

Related content

I want to calculate Aging category based on date selection

Calculate Average per category

Calculating cumulative total across categories

calculate max per category.

Calculate growth based on calculation group item