Cannot figure out how to report on this data by month by user

[Anonymous]

Gooday all,

I have the following data i need to report on:
username, datetime, data1,data2,data3,data4
ted, 1/04/2019, 1,2,1,2
eric, 4/04/2019, 1,1,2,2
frank, 5/04/2019, 1,2,1,2
erica, 5/04/2019, 1,1,2,2
ted, 5/05/2019, 1,2,0,2
eric, 6/05/2019, 1,2,2,2
frank, 7/05/2019, 2,2,1,2
erica, 7/05/2019, 1,3,2,2

The report required is by user per month
Month: April 2019
              ted        eric      frank        erica
Data1      1           1           1              1
Data2      2           1           2              1
Data3      1           2           1              2
Data4      2           2           2              2


Any suggestions?

Thank you

[MFelix]

Hi @Anonymous ,

On the query editor select the Data1, Data 2, Data 3 and Data 4 and unpivot the columns then just use the matrix visual with the following setup:

• Rows: Attribute (name after unpivot you can change the name)
• Columns: User
• Values: Value

Should give expected result.

Regards,

MFelix

[Anonymous]

@MFelixApologies for the late reply and thank you for replying. I appear not to have been notified that you had posted and I only check in today.

I am linking to a sharepint list as my source and am a PoerBI noob! I see I get a list of fields on the right and using the Matrix table as you suggested I can add the user field to columns but then I get confused 😞

For the Rows do I add each field(Data 1-4)?

Where do I get the value from if it is not trhe field values Data 1-4?

[MFelix · Accepted solution]

Hi @Anonymous ,

Before making the matrix table you need to go to the query editor, select the data 1 to 4 and unpivot columns

Check the PBIX file attach with all the steps:

Regards,

MFelix