Power BI - Use Column name to create Row and attach Value from that Row to it

user_13843270 0
Viewed 42

I have a table from a survey that reports the score given to a specific employee, and various columns are there to hold each score for each question. Like this table below: Table I have example

Now, what I want to do is make a row for each question, and in the original table, each question is a column. And I'd like for example, John, to have 1 entry for each question, and the average of that score stored next to each question like in this table here:

Table I want example.

This shows clearly what I'm aiming for.

I believe I need some sort of pivot or unpivot table going on, but I'm not too sure on the Power BI DAX syntax for creating a new table.

I currently have a table that provides each Employee once, and columns showing their average score for each question, but that is a bit harder to dice up the way I want to. Code pasted below:

    ReportTable = 
SUMMARIZE(
    ALL ( '360Sample'[Name], '360Sample'[Relationship to person being reviewed]),
    '360Sample'[Name],
    '360Sample'[Relationship to person being reviewed],
    "Employee Satisfaction", DIVIDE(CALCULATE(AVERAGE('360Sample'[Treats others with respect/Truly values employees]))+AVERAGE('360Sample'[Encourages and supports staff in developing their skills])+AVERAGE('360Sample'[Provides effective mentoring]),3),
    "Quality Product", DIVIDE(CALCULATE(AVERAGE('360Sample'[Consistently strives to provide products above industry quality standards]))+AVERAGE('360Sample'[Takes ownership of project outcome])+AVERAGE('360Sample'[Ensures quality control always happens on products]),3),
    "Client Satisfaction", DIVIDE(CALCULATE(AVERAGE('360Sample'[Fosters open, honest, and consistent communication with clients]))+AVERAGE('360Sample'[Responds quickly to client questions and concerns.])+AVERAGE('360Sample'[Successfully communicates contractual needs and requirements with the Client, including schedules and scope and fee increases]),3),
)

Only difference here is that I have an extra attribute of "Relationship" which I'll also need to include but is less important for now. It makes a row of each employee for every unique Relationship, which is 2.

dax powerbi
1 Answers

Hello You need to first use the "unpivot" in Power Query to convert your table into this shape: It is not so hard.

Like this:

Unpivot

FKT

Then use this DAX Code:

ReportTable =
ADDCOLUMNS (
    SUMMARIZE ( 360Sample, 360Sample[Name], 360Sample[ScoreNum] ),
    "ScoreAvg", CALCULATE ( AVERAGE ( 360Sample[Score] ) )
)

And It produces:

GGGGG

user_194690880
Related