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