reporting how to

[iruserwin]

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!

[Irwan · Accepted solution]

hello @iruserwin 

i think you should get your desired result from unpivot table

- unpivot table

- split Attribut column by space delimiter at the leftmost

- plot in matrix visual

Hope this will help.

Thank you.

[iruserwin]

thank you both... that is exactly what i was looking for.

As you can tell my sample data was fake.  Now i have to figure out how to apply this solution to the actual 20 columns of data.

[Irwan]

hello @iruserwin 

glad to be a help.

Thank you.

[ryan_mayu]

@iruserwin 

have you tried unpivot in PQ

https://learn.microsoft.com/en-us/power-query/unpivot-column?wt.mc_id=DP-MVP-5004616