Forum Discussion
Matrix and Tables - multiple measures layout question
For those that agree this needs to be fixed, please vote for it here
You are correct - the table and matrix functionalities are very limited in Power BI. I did see few improvements when they showcased features around conditional formatting etc. in the recent Data Insights Summit. However, we are not sure when these would be released. Even if these features get release, they still have a long way to go offer the same flexibility as Excel pivot tables.
You are on the right track in creating a disconnected table and using "switch" to apply the calculations. However, we had few issues with this approach mostly around formatting as you can have different format strings for different measures ($, Units, % etc.). You could use the format function in DAX but this would convert each of your measure to a text. It's not flexible as you need to expand the "switch" statement everytime you add a new measure. Also, there are limitations in few use cases around charting, e.g. when you want to use a combo chart/scatter chart (or any other chart which would require more than one measure) you wouldn't be able to use the measures from you "Measure" dimension (disconnected table).
Thought I'd let you know the limitations based on our experience using a similar approach (just in case you are not aware)