Forum Discussion

MelB_EIA's avatar
MelB_EIA
New Member
4 years ago

Calculation based on text field category

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 o...
Syk's avatar
Syk
Resident Rockstar
4 years ago

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's avatar
MelB_EIA
New Member
4 years ago

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:

 

IDDateType of gasReported volumeEstimated volumeTotal volume
101/02/2022CFC-1112000.000.0012000.00
202/02/2022HCFC-5020.0058000.0058000.00
202/02/2022HFC-23800.000.00800.00
303/03/2022HCFC-406A542.000.00542.00
404/02/2022CFC-110.008795.008795.00
505/02/2022HFC-230.007845.007845.00
505/02/2022HFC-5070.005274.005274.00

 

Example of GWP value table:

 

Type of gasGWP
Bromochloromethane6900
CFC-115560
CFC-1124620
CFC-1136520
CFC-1211200
HCFC-5024657
Halon 12111930

 

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

 

Any ideas? Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      MelB_EIA
      New Member
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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