Create a new table from the average value of each columns

[phumengam]

Hi All,

I am trying to create a new table to display on the report where each row consists of column name and the average value of that column. See the pic, https://ibb.co/hcRTkg5. 

My original data would look something like this:

Factor 1 | Factor 2 | Factor 3

-------------------------------

      1      |        2     |       4     |

      9      |        6     |       3     |

      1      |        7     |       1     | 

The output table that I am looking for would look like this:

Factors |  Scores |

-------------------

Factor1 | Average(Factor1) |

Factor2 | Average(Factor2) |

Factor3 | Average(Factor3) |

I followed this link: https://community.powerbi.com/t5/Desktop/Create-table-from-measures/m-p/275686 but it doesn't seem to work.  Here is my code: 

````

Table =
UNION(ROW("Factor","Factors","Score", "Scores")
, ROW("Factor","Factor1","Score", [avg_1])
, ROW("Factor","Factor2","Score", [avg_2])
, ROW("Factor","Factor3","Score", [avg_3]))

```

This is the error message I received: "the expression refers to multiple columns. multiple columns cannot be converted to a scalar value"

Thanks.

[amitchandak · Accepted solution]

@phumengam , Try like given below

Table =
UNION(
ROW("Factor","Factor1","Score", [avg_1])
, ROW("Factor","Factor2","Score", [avg_2])
, ROW("Factor","Factor3","Score", [avg_3]))

Have you explored option, "Show on Row" in Matrix, which can show measure on row

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

Another option is to unpivot or transpose the table 

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[FarhanAhmed]

Try This to create new table.

Table = 
UNION(
 ROW("Factor","Factor1","Score", AVERAGE(Factor[Factor 1 ]))
, ROW("Factor","Factor2","Score", AVERAGE(Factor[ Factor 2 ]))
, ROW("Factor","Factor3","Score", AVERAGE(Factor[ Factor 3])))

[phumengam]

Do you by any chance know if we can make the filter work with this table measure?

All data (no filter):

Applying a filter: The filter is working with card but it doesn't work with the measure table. 

Do you know if we can apply the filter on this table?

[phumengam]

I received the same error.

Note that my table is called "ee_Data modeling_result" and the columns are called score_v1, score_v2,...

Here is what my sample data looks like: 

[FarhanAhmed · Accepted solution]

You should create a new table instead of a new column in order for this code to work.

[phumengam]

This works!  I have spent quite a few hours on this.  Thanks a lot, @FarhanAhmed.

[amitchandak · Accepted solution]

@phumengam , Try like given below

Table =
UNION(
ROW("Factor","Factor1","Score", [avg_1])
, ROW("Factor","Factor2","Score", [avg_2])
, ROW("Factor","Factor3","Score", [avg_3]))

Have you explored option, "Show on Row" in Matrix, which can show measure on row

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

Another option is to unpivot or transpose the table 

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[phumengam]

Thank for your response. 

1. When I tried the code, I received the same error. 

2. I'm not sure how can I leverage the "Show on Row". I think that I still need to construct a table of summary statistic first then I can do the "Show on Row".

3. I was thinking about pivoting the data but I have other visualization objects that relies on the same data. So, is it possible to create seperate pivot table that can be linked back to the original table?

[amitchandak]

@phumengam , The first one should not give an error, if you are creating a table.

Put these three measures on matrix value, And use show on row and check