I have data in the format shown in the diagram below. It came from a rough and ready spreadsheet and i had to compile a quick and dirty report. Simple enough after importing into SQL Server.
But I also tried with powerpivot and had a lot of issues and am wondering if this data is not ideal for powerpivot.
Issues:
- when i dragged on Applicant Name from the Applicant Table & qualification Name from the qualifications table, each applicant had ALL the qualifications under them, not just their qualifications. I fixed this by creating a dummy measure on the qualification table, but this seems like a terrible workaround.
-when i dragged on the Score_PartA, the sum was not correct. It summed up the value based on all the qualifications. e.g. if the applicant scored 5 on partA, and the applicant had 5 qualifications, the measure showed 25. i got around this by selecting the MAX, but again, this feels hacky.
Id appreciate any feedback on how i could approach this better.