Attribute Value (summing up columns after unpivoting them)

[Anonymous]

Hello Community, I have an Excel sheet that I am bringing into Power Bi.   The Excel sheet has about 50 columns that cover different inspection criteria, and about 100 rows (of different items).   Each item is one row.   

After bringing into Power Bi, I unpivoted the inspection columns.  So now, all of the inspection criteria are in one column, and the Pass/Fail is in another column.   What I am trying to do now is to create a measure that sums up all of the "P"  and all of the "F" for each particular attribute.   Just not sure how to go about how to go about it.  With the goal of having a matrix that could look like this: 

                    Inspection Criteria 1      Inspection Criteria 2       Inspection Criteria 3    Etc...

Pass                 98                                           97                                    89

Fail                   2                                             3                                      11

This is the Excel layout  (there are many rows of items...each item is one row)

To the left of the columns below is another column called Item.    I unpivoted the columns above, to get all of the attributes in column, and the Pass / Fail in another column

[Anonymous · Accepted solution]

There's no need to create a measure once you have this table unpivoted. You only need to create a matrix visual!

See image below on how should be built and I leave you a sample file you can leverage from. (I simulated your table entering data manually)

Note: You can have the matrix structured 2 ways. I would suggest you the one of the left since you mention you have a lot of inspection criteria.

File Here 

[Anonymous]

@Anonymous   Thanks Ray...I am giving your option a try as well!     Will report back and give kudos to both once I can successfully confirm the solution.   Working on it now!

[c781d]

Hi @Anonymous 

If I unerstand your problem, you have this

And is this your goal?

[Anonymous]

Yes, exactly! 

[c781d]

Nice!

Paste this step on your queryeditor

= Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[atribute]), "atribute", "item", List.Count)

Regards!

[Anonymous]

Which step in the query?   

[c781d]

https://drive.google.com/file/d/1-d4NgQ6FXdklr0EZQYxEbHWzv2Hj-4pD/view?usp=sharing

[c781d]

Please, Replace your last two lines with:

#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Changed Type"[atribute]), "atribute", "item", List.Count)
in
#"Pivoted Column"

Make sure that the #"Unpivoted Colums" line fishes with comma

[Anonymous]

Hmmm...I am getting an error message.   Saying the column Attribute of the table was not found?

[c781d]

Sorry, I forgot to tell you to change also the second #"Changed Type".

Please, change the #"Changed Type" into List.Distinct for #"Unpivoted Columns".

#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Item", List.Count)
in
#"Pivoted Column"

It should work now

[Anonymous]

Hey @c781d  Now I am not getting an Attribute column, but did successfully load the model.   Are you available for a quick consultation (not free of course!) 

[Anonymous]

Here is how my value column now looks after apply the m code.   It contains both a numeric value and a P  or  F  value....so something is not quite right

[c781d]

Could you please share your file with me?

In thatt way I could back to you with the solution

Thanks

[Anonymous]

Unfortunately I cannot share the file.   Actually, the solution provided by @Anonymous  worked and he was right, I did not need to do any further edits to the query editor after I did the initial unpivot.    I could see where your solution might have eventually worked as well, but it was giving me strange results.   

[c781d]

Glad to help you.

Yes, ask what ever you want. Lets see if i can get it