Matrix using multiple columns w/ same pick list values

[Anonymous]

Hi all,

Been searching for some time and haven't found a good solution to an issue we're having in terms of building out a matrix that takes multiple columns that all have the same pick list values and summarizes them based on those that we could then slice based on other dimensions within the same table.

I have a single table at the moment w/ all of our data.  Simplified, the data looks like the following:

Deal#	Budget	Tech	Plan	Fit	Value
1	Yes	No	Yes	Yes	10
2	No	Yes	Maybe	Maybe	15
3	Yes	No	No	Yes	10
4	Yes	No	Yes	Yes	15
5	Yes	Maybe	Yes	No	20
6	No	Yes	Yes	No	5
7	No	Maybe	Maybe	Maybe	10
8	Maybe	Yes	No	No	5
9	Yes	Yes	Yes	Yes	10

What we'd then like to build out is a matrix like the following:

Sum of Value

	Yes	No	Maybe
Budget	65	30	5
Tech	35	35	30
Plan	60	15	25
Fit	45	30	25

Is this possible?  Much appreciated, thanks!

[PaulDBrown]

The easiest way is to unpivot the table to get the "Types in rows":

You can then create three measures (for Yes/No/Maybe) using the following:

Yes =
CALCULATE ( SUM ( Unpivoted[Value] ), Unpivoted[Criteria] = "Yes" )

Create the matrix by putting "type" in the rows bucket and add the three measures to get:

I've attached the sample PBIX file

[Anonymous]

Thanks for the quick response as well as the file.  Quick follow up question; if the backend data refreshes daily, is there a way to automate this?  I created a dummy file but due to restrictions can't share from my onedrive nor can i see a way to upload here.  The actual data is over 100k rows and 50+ columns, but I think the methodology makes sense (this would be the first time using the unpivot function as i'm still new to PBI), but I'm not sure if it's something that would run daily.  Thanks again!

[PaulDBrown]

Sure. The unpivoting takes place as a step in Power Query, so it will create the unpivoted table automatically once it is set up. 
You can share the dummy file using a cloud service or something like wetransfer

[Anonymous]

that's great to hear.  Here's an attempt at sharing the dummy file

https://we.tl/t-m2khJgW8lg 

[PaulDBrown · Accepted solution]

See if this works:

You can get the following by unpivoting and a simple SUM measure

I've attached the PBIX file

Please beware you will need to change the source of the file

[Anonymous]

Thanks again!

[Anonymous]

can you share your powerbi file?

[Anonymous]

see the following link

https://we.tl/t-m2khJgW8lg