Forum Discussion
Helper ICreate a matrix to display different sums / counts based on columns
Sorry for the not-very-descriptive title.
Basically, I have a table that looks like this:
| ID | Charge 1 | Charge 2 | Charge 3 | Charge 4 |
| 1 | 2.52 | 3.21 | null | 9.21 |
| 2 | null | 4.58 | null | 3.81 |
| 3 | 4.77 | 8.23 | null | 1.78 |
What I'd like to be able to do is build a matrix that looks like this:
| Charge Name | Count | Sum | Avg |
| Charge 1 | 2 | 7.29 | 3.65 |
| Charge 2 | 3 | 16.02 | 5.34 |
| Charge 3 | 0 | 0 | 0 |
| Charge 4 | 3 | 14.80 | 4.93 |
Is there a way to do this? My limitations: using May 2023 Report Service application and need to maintain a direct query connection.
2 Replies
- christinepayton
Most Valuable Professional
Yeah, you need to unpivot in Power Query first. Right click on the ID column in PQ and do "unpivot other columns". Then create your measures for the average, count, and sum and drop them into the "values" in a matrix (with the attribute column in rows). You can rename the values/attribute columns, too, just using it as a reference here.
bensiqcIf I do that, I'm given a message saying that this will result in a table that cannot be used in DirectQuery mode. Is there any solution I can use that won't result in this?
