Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Helpful_Fun4848
Helper III
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).    

 

 

Helpful_Fun4848_0-1636485081460.png

 

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

 

1 ACCEPTED SOLUTION

Hi @Helpful_Fun4848,

 

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

 

BEFORE:

Division1.PNG

 

AFTER:

Division2.PNG

 

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

 

mattww_1-1636652371000.png

 

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

 

let
Source = 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.

View solution in original post

5 REPLIES 5
mattww
Responsive Resident
Responsive Resident

Hi @Helpful_Fun4848,

 

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

 

IDColumnLabelColourValue
1Column 1Pink1
1Column 2Pink1
1Column 3Orange1
1Column 4Orange1
1Column 5Orange2
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.

 

@mattww 

 

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

 

So, this is what it looks like originally:

 

Helpful_Fun4848_0-1636492181568.png

 

After I Unpivot it becomes this:

 

Helpful_Fun4848_1-1636492238616.png

 

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

 

Helpful_Fun4848_2-1636492301905.png

 

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

Hi @Helpful_Fun4848,

 

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

 

BEFORE:

Division1.PNG

 

AFTER:

Division2.PNG

 

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

 

mattww_1-1636652371000.png

 

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

 

let
Source = 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.

@mattww 

 

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?

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

 

mattww_0-1637082592250.png

 

mattww_1-1637082619519.png

 

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

 

Does that answer your question?

 

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

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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