Find the highest and lowest column averages from a range

[SBlake90]

Hi,

I have a range of attributes (e.g. price 1, price 2, price 3, price 4), which have been individually scored in a survey according to preference. Each attribute is a separate column, with the individual scores in the rows.

I need to calculate the column average for each, and then which column in the range has the highest average and which has the lowest average. i.e. which has the highest average score and which has the lowest.

I know I can do this with a visual, however I want to do this with a measure as I then need to use these numbers for further calculations. I also need these numbers to adjust as the data is filtered, so I can't just calculate them and use the static numbers.

I'm still learning DAX so I'm hoping there is an easy solution that I've not thought of yet. 

[v-alq-msft]

Hi, @SBlake90 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create three measures as below.

Avg = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Name],
    'Table'[Price1],
    'Table'[Price2],
    'Table'[Price3],
    "Avg",
    var p1 = [Price1]
    var p2 = [Price2]
    var p3 = [Price3]
    return
    AVERAGEX(
        {p1,p2,p3},
        [Value]
    )
)
return
AVERAGEX(
    tab,
    [Avg]
)

Max = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Name],
    'Table'[Price1],
    'Table'[Price2],
    'Table'[Price3],
    "Max",
    var p1 = [Price1]
    var p2 = [Price2]
    var p3 = [Price3]
    return
    MAXX(
        {p1,p2,p3},
        [Value]
    )
)
return
MAXX(
    tab,
    [Max]
)

Min = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Name],
    'Table'[Price1],
    'Table'[Price2],
    'Table'[Price3],
    "Min",
    var p1 = [Price1]
    var p2 = [Price2]
    var p3 = [Price3]
    return
    MINX(
        {p1,p2,p3},
        [Value]
    )
)
return
MINX(
    tab,
    [Min]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SBlake90]

Thanks @v-alq-msft 

The average calculation works, however the max and min measures just return the highest and lowest score for each price. I need to find which price has the highest and which the lowest average out of the 4 prices.

[v-alq-msft]

Hi, @SBlake90 

You may modify the measures as below.

Max = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Name],
    'Table'[Price1],
    'Table'[Price2],
    'Table'[Price3],
    "Avg",
    var p1 = [Price1]
    var p2 = [Price2]
    var p3 = [Price3]
    return
    AVERAGEX(
        {p1,p2,p3},
        [Value]
    )
)
return
MAXX(
    tab,
    [Avg]
)

Min = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Name],
    'Table'[Price1],
    'Table'[Price2],
    'Table'[Price3],
    "Avg",
    var p1 = [Price1]
    var p2 = [Price2]
    var p3 = [Price3]
    return
    AVERAGEX(
        {p1,p2,p3},
        [Value]
    )
)
return
MINX(
    tab,
    [Avg]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SBlake90]

@v-alq-msft, that's returning nil values for some reason...

[v-alq-msft]

Hi, @SBlake90 

Could you show me some sample data and expected result with OneDrive for business? Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

[parry2k]

@SBlake90 I would recommend unpivoting your data and that will make it very easy to work with. Select all the columns in power query except Price 1 to Price 4, right-click and unpivot other columns.

It will add two columns, attribute, and value, rename these as per your requirement. Add the following measures

Average = AVERAGE ( Table[Value] )

Max Average = MAXX ( VALUES ( Table[Attribute] ), [Average] )

Min Average = MINX ( VALUES ( Table[Attribute] ), [Average] )

You can take it from here and add another measure as per your requirement.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[SBlake90]

Thanks @parry2k

 I think that would have worked, except I have groups of attributes (brand, privacy etc.) so the two columns get a bit complicated. Any way to split them out? Sorry, should have specified this in the original post.