Forum Discussion
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!
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.
5 Replies
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.
