Forum Discussion
Solved
reporting how to
Hi fabric folks. I'm not sure if this is a reporting, power query, or dax question.
Given data set
| client | admin revenue type 1 | admin revenue type 2 | admin revenue type 3 | other revenue type 1 | other revenue type 2 | other revenue type 3 |
| bob | 10 | 20 | 25 | 22 | 55 | 88 |
| mary | 20 | 25 | 30 | 33 | 66 | 99 |
| jane | 25 | 30 | 20 | 44 | 77 | 10 |
and desired report
| admin | other | |
| revenue type 1 | 55 | 99 |
| revenue type 2 | 75 | 198 |
| revenue type 3 | 75 | 197 |
I dont have seperate records for each revenue type they are instead columns so neither a matrix or table are working for me.
I think a DAX total field for each revenue type would perhaps work. Is that the smartest way however?
Is there a DAX for the label revenue type 1 revenue type 2?
Is there a way to make the data become multiple rows?
| client | admin revenue type 1 | admin revenue type 2 | admin revenue type 3 | other revenue type 1 | other revenue type 2 | other revenue type 3 |
| bob | 10 | 0 | 0 | 0 | 0 | 0 |
| bob | 0 | 20 | 0 | 0 | 0 | 0 |
| bob | 0 | 0 | 25 | 0 | 0 | 0 |
| bob | 0 | 0 | 0 | 22 | 0 | 0 |
| bob | 0 | 0 | 0 | 0 | 55 | 0 |
| bob | 0 | 0 | 0 | 0 | 0 | 88 |
or even better
| client | revenue type | amount |
| bob | admin revenue type 1 | 10 |
| bob | admin revenue type 2 | 20 |
| bob | admin revenue type 3 | 25 |
| bob | other revenue type 1 | 22 |
| bob | other revenue type 2 | 55 |
| bob | other revenue type 3 | 88 |
Open to suggestions as to how to proceed.
thanks!
4 Replies
have you tried unpivot in PQ
https://learn.microsoft.com/en-us/power-query/unpivot-column?wt.mc_id=DP-MVP-5004616
