Forum Discussion
How to calculate average from multiple columns
Hi Helpful_Fun4848,
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
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.
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
| 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.
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!
Hi Helpful_Fun4848,
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
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.
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
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.