Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a table set-up as below:
I'd like to use a matrix to set it up similar to a pivot table:
I've tried different ways and can't get it to work. I've put BDO and State in the Rows, and have created measures for totals of phase 1, 2, and 3 and put that in the values. But for example, the total for Phase 1 is repeating the grand total, not the total by BDO by State. This is the formula I used:
The measure you need is a simple SUM for each Phase. The fields in the rows provide the filter context you need
Having said that, I would model your data differently.
Table 1 would include BDO, State and the Phases Unpivotted. You can then create a simple SUM for the values and set up the matrix with the phases field (from the diemnsion table- see below) as the columns for the matrix.
Table 2 would exclude the phases columns.
Table 3, 4 and 5 would be dimension tables for Phases, BDO and State, with one-to-many relationships between the corresponding fields in Table 1 and 2.
However this decision very much depends on what you need to depict in the report
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |