CODEWITHSUNDEEP
transformpowerbidax
ShieldData
ShieldData

Reputation: 125

Creating Table with averages of Columns

I need some Power BI magic. I've got a few columns filled with different duration for steps of a process, and I need to put the steps into rows and the averages of the columns into a row next to it.

In other words, the input is multiple columns all with lots of values in it, and the output is a table with 2 columns, one for the name of the process step and the other for the average value of that process step. I've attached an example of what I need to this post.

The table is too long to transpose it, and I've been playing around with AVERAGEX, but without any success. I would know how to do it in Excel but I am too new to Power BI to make it work. I need the DAX formula to make a transformation like that.

Thanks a lot for your help in advance. enter image description here

Upvotes: 1

Views: 147

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

The easiest way to do this is probably with the Query Editor.

From the input table, select all of the step columns and choose Unpivot Columns from the Transform tab at the top.

Once they are unpivoted, you can do a Group By on the Home tab. Group by the attribute column and average over the value column.

Edit: You can do the averaging with DAX instead if you like, but the key part here is the unpivoting of the columns.

Upvotes: 1

Related Questions