cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## How to calculate average from multiple columns

Hi all,

I have more than 1 columns where I want to get the average.

The table looks like this, so I need to get the total average for each color group (2 columns altogether for 1 color and 3 columns for the other color).

What would be the measure for that? Thx in advance!

1 ACCEPTED SOLUTION
Responsive Resident

You probably need to right click on Division (the column you want to maintain) and click Unpivot other columns

BEFORE:

AFTER:

When you do this, it will take all the other column headers (Column 1, 2, 3, etc) and put them onto rows in the Attribute column. The values from the cells underneath each column will be put alongside in the newly created Values column. That means you can then average the Values column, applying any filters you want to the Attribute or Division column.

Now it is in this shape, this allows you to create a conditional column based on which column (1,2,3 etc) the value came from, so the result of this can also be used to achieve your average based on the colour (in your example).

I have copied your sample data below and applied the steps described, you will be able to copy and paste this into Power Query (New Source > Blank Query > Advanced Editor) so you can see the steps for yourself

`letSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTJEw0ZKsTrRSDwENgTLmKGoRZYxRRI1hmLsphnC9RiimQaViQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Division = _t, #"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t]),#"Changed Type" = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Column 1", Int64.Type}, {"Column 2", Int64.Type}, {"Column 3", Int64.Type}, {"Column 4", Int64.Type}, {"Column 5", Int64.Type}}),#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Division"}, "Attribute", "Value"),#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "ColumnColour", each if [Attribute] = "Column 1" then "Pink" else if [Attribute] = "Column 2" then "Pink" else if [Attribute] = "Column 3" then "Orange" else if [Attribute] = "Column 4" then "Orange" else if [Attribute] = "Column 5" then "Orange" else "Other")in#"Added Conditional Column"`

Hope that helps, let me know if you need any more info

Matt

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

5 REPLIES 5
Responsive Resident

I don't think there is a DAX expression which naturally achieves what you're after, but as a very basic solution, you could do Calculated Columns, i.e.

(Calc Column) Pink Total = [Column 1] + [Column 2]

(Calc Column) Pink Count = 2

(Measure) Pink Average = DIVIDE(SUM([Pink Total],SUM([Pink Count]),0)

I suspect that might not be ideal, so the other way I'd approach it is to Unpivot your data in Power Query so it reads something like this

 ID ColumnLabel Colour Value 1 Column 1 Pink 1 1 Column 2 Pink 1 1 Column 3 Orange 1 1 Column 4 Orange 1 1 Column 5 Orange 2 etc

Then you could just AVERAGE() the Value column, and apply filters/slicers to the ColumnLabel and Colour columns

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

Helper III

I tried to Unpivot, but, it doesn't give me the same result.

So, this is what it looks like originally:

After I Unpivot it becomes this:

The division which was on the first column, moves to the last one and there is a new Attribute column created:

I'm new to Power BI, so, I most likely missed something.  Much thanks for your help!

Responsive Resident

You probably need to right click on Division (the column you want to maintain) and click Unpivot other columns

BEFORE:

AFTER:

When you do this, it will take all the other column headers (Column 1, 2, 3, etc) and put them onto rows in the Attribute column. The values from the cells underneath each column will be put alongside in the newly created Values column. That means you can then average the Values column, applying any filters you want to the Attribute or Division column.

Now it is in this shape, this allows you to create a conditional column based on which column (1,2,3 etc) the value came from, so the result of this can also be used to achieve your average based on the colour (in your example).

I have copied your sample data below and applied the steps described, you will be able to copy and paste this into Power Query (New Source > Blank Query > Advanced Editor) so you can see the steps for yourself

`letSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTJEw0ZKsTrRSDwENgTLmKGoRZYxRRI1hmLsphnC9RiimQaViQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Division = _t, #"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t]),#"Changed Type" = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Column 1", Int64.Type}, {"Column 2", Int64.Type}, {"Column 3", Int64.Type}, {"Column 4", Int64.Type}, {"Column 5", Int64.Type}}),#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Division"}, "Attribute", "Value"),#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "ColumnColour", each if [Attribute] = "Column 1" then "Pink" else if [Attribute] = "Column 2" then "Pink" else if [Attribute] = "Column 3" then "Orange" else if [Attribute] = "Column 4" then "Orange" else if [Attribute] = "Column 5" then "Orange" else "Other")in#"Added Conditional Column"`

Hope that helps, let me know if you need any more info

Matt

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

Helper III

Thanks, it works!

Do you know how do I create a parent group of multiple columns? So, say I want to put the 3 orange columns as Care, and the 3 pink columns as Passion.

Right now, if I put the result onto a Pie Chart or Donut Chart, it will show the average for each attribute without parent group. How do I create the chart to not only show the average for each attribute but also, the parent group comprising of the average of all attribute under that group?

Responsive Resident

Hi @Helpful_Fun4848, sorry for the delay in responding to you

The parent groups should already be taken care of in the example I sent. Obviously changing the outputs of the conditional column from Pink/Orange to Passion/Care

If you use that column in your pie chart, that should give you the average over your parent group, you could even drag in the attribute under that so you can drill down to see the split out values

Users would use the highlighted columns to navigate between the drill up/drill down. You could alternatively just have them as two separate charts

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors