Forum Discussion
Matrix using multiple columns w/ same pick list values
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
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 wetransferthat's great to hear. Here's an attempt at sharing the dummy file