Forum Discussion
Matrix using multiple columns w/ same pick list values
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!
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
8 Replies
see the following link
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
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!
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