Finding a column with the lowest average value

[MyWeeLola]

Good afternoon

I have a table with 4 columns. ColA ColB ColC and ColD. they contain numerical values.

If I do a sum or an average of the values in each column, I get a number for each. I need to make a decision based on which column contains the lowest sum or average value. How do I get a return that states which column returns the lowest value.

If the returned value is "ColA", Then that allows me to present information on another card. How would I begin to do this?

I appreciate any direction or help in this area.

Lola

[MyWeeLola]

@tamerj1 thank you for the responses. All is working as it should, thank you.

[tamerj1 · Accepted solution]

Hi @MyWeeLola 
Please try

Lowest Sum =
MAXX (
    TOPN (
        1,
        ADDCOLUMNS (
            { "ColA", "ColB", "ColC", "ColD" },
            "@Sum",
                SWITCH (
                    [Value],
                    "ColA", SUM ( TableName[ColA] ),
                    "ColB", SUM ( TableName[ColB] ),
                    "ColC", SUM ( TableName[ColC] ),
                    "ColD", SUM ( TableName[ColD] )
                )
        ),
        [@Sum], ASC
    ),
    [Value]
)

[MyWeeLola]

@tamerj1 Thank you for the response. Am I correct in assuming that if I change the MAXX to MINX, I will get the lowest value?

Lola

[tamerj1]

@MyWeeLola 
Acually no you will get the same value in both cases. The reason is TOPN returns only one record (assumining no ties) therefore it does not matter whether you use MINX or MAXX. IF you want the max value you need to delete ASC from inside TOPN.
However, it is also good idea to concatenate the results in case of ties such as:

Lowest Sum =
CONCATENATEX (
    TOPN (
        1,
        ADDCOLUMNS (
            { "ColA", "ColB", "ColC", "ColD" },
            "@Sum",
                SWITCH (
                    [Value],
                    "ColA", SUM ( TableName[ColA] ),
                    "ColB", SUM ( TableName[ColB] ),
                    "ColC", SUM ( TableName[ColC] ),
                    "ColD", SUM ( TableName[ColD] )
                )
        ),
        [@Sum], ASC
    ),
    [Value],
    UNICHAR ( 10 ),
    [Value], ASC
)