Good morning,
I am building a Market Model looking at our Market Share versus competitors by Hospital and Surgeon.
My first attempt has not worked and only showed the totals for the first company selected
I am collecting in a Excel file with the columns:
State, Sales Rep, Hospital, Surgeon, monthly usage, Our usage, One column for each competitor usage
NOTE: Surgeons will work across several Hospitals
What I can’t work out is if I bring this into the Query Editor, how can I make sure that each column sums up independantly so I can see Share by Sales Rep, Share by hospital, Share by Surgeon. I also want to rank each company based on usage. My Excel columns are attached.
Do I need to change the data collection excel file or do I just need to do something different in the QUery editor?
@KiwiNigel
I think you should be fine bringing the information into the power query within Power BI. Only bring in raw data with no calculation. I would setup a doctor, hospital and sales Rep into separate sheets within excel. The doctor, hospital and sales will be reference number into the raw data sheet. This way if any new one comes onboard you don’t have to change within raw data, only one place you need to update and easy to refresh within power bi.
For me, i just want keep things simple as possible.
I’m not sure if you have watched some of the video that EDNA has produced on youtube channel. That might be a good start and might give you some ideas also. The videos are really good from importing data to making up your own calculation measures to start putting information in a visual presentation.
You can load all the information with power query editor and check/change column headings before you make final load into power bi.
I’m sure you know that you can check your work by using pivot table within excel on your data and verify those results that power bi is calculating.
What i have read within the forum, there is a lot of dax calculation to help produce your report.
I’m learning a lot within using power bi.
Good luck with your project and happy learning
Thanks
Keith
Hi @KiwiNigel, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
How I ended up approaching this is to create Pivot tables in the Excel file for Each Company
Then import these into PowerBI and use the Query Editor to clean them and then combine them into one data set.
It is working well now
