Forum Discussion
advice on data
I have created a working example pbx file for you showing step by step how you can take youe excel file and make it work in PowerBI.
Datawise at a minimum I suggest you transpose your data from this
Sale person Jan-19 Feb-19 Mar-19 Apr-19 Peter 2 3 4 5 Alan 1 2 4 5 John 5 7 8 8 To this
Date Peter Alan John Jan-19 2 1 5 Feb-19 3 2 7 Mar-19 4 4 8 Apr-19 5 5 8 You can do this with special paste in excel and select transpose.
Better still you would have
Salesperson Date Value Peter Jan-19 2 Peter Feb-19 3 Once you have your data better set up the powerbi side is easier.
In the example pbx file I also created a seperate date table by merging the salesperson and location tables together, removing everything except the dat and then deleting duplicates.
This allows you to add a date filter that will show all dates that appear in either the sales person or location tables.
Even better would be to create a date dimension but given you are just starting out what I have described above is easier for now.
If you want the example pbx file then just private message me your email address and I will send it over.
Hope this helps.
If so please click the botton to accept this as an answer.
Thanks.
Hi Anonymous ,
As tested, without changing the original data, you can get the visual as below in Power BI:
Table：
Matrix:
Even if the data is split into two tables, the two cannot establish a relationship.
So it is better to split the data into two tables and display them in two visuals.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.