Forum Discussion
Resolver IProblems modelling Excelfile for use in PBI
OK, here is how I would go about it. It is a different method to yours though...
1) Unpivot the data on import: select the period and date columns and under unpivot choose "unpivot other columns"
2) Add a conditional column to create a new column classifying categories as right or wrong:
and
Your Fact table should now look like this (I´ve actually deleted the period column you had, since I always include it in my calendar table):
3) Create a Calendar Table
4) I've also created a Category Table by referencing from the fact table, deleting all columns except the Category and Right or Wrong Columns, and deleted duplicate values. The table looks like this:
5) Load all into the model and create the relevant relationships between tables. The model should resemble this:
6) Create any measures you need for the pie chart; I've just made a simple sum of values.
7) Create your pie chart using the "Right or Wrong" and "Category" fields from your Category Table as your legend and your measure as values:
You can now drill down/up etc on your pie chart (by right-clicking on a segment or using the drill-triggers on the chart):
and once you have "drilled-down":
And here is the PBIX file if you're interested:
Pie Chart ExampleHope it helps, and let us know if something isn't working for you!
Community ChampionPLease provide a sample of your data, data structure and model structure
JackofallPeriod Date A B C D E F G 2019-09 2019-09-01 277 10 17 2 24 55 8 2019-09 2019-09-02 269 19 14 3 25 53 28 Above is Excel. I want to group Columns A, B and C. Group D,E,F and G
I have imported that Excel as it is and I made two separated sheets of the different groups as a laboration.I dont know how to add files on this URL so I added a table and photos.
- PaulDBrown
OK, here is how I would go about it. It is a different method to yours though...
1) Unpivot the data on import: select the period and date columns and under unpivot choose "unpivot other columns"
2) Add a conditional column to create a new column classifying categories as right or wrong:
and
Your Fact table should now look like this (I´ve actually deleted the period column you had, since I always include it in my calendar table):
3) Create a Calendar Table
4) I've also created a Category Table by referencing from the fact table, deleting all columns except the Category and Right or Wrong Columns, and deleted duplicate values. The table looks like this:
5) Load all into the model and create the relevant relationships between tables. The model should resemble this:
6) Create any measures you need for the pie chart; I've just made a simple sum of values.
7) Create your pie chart using the "Right or Wrong" and "Category" fields from your Category Table as your legend and your measure as values:
You can now drill down/up etc on your pie chart (by right-clicking on a segment or using the drill-triggers on the chart):
and once you have "drilled-down":
And here is the PBIX file if you're interested:
Pie Chart ExampleHope it helps, and let us know if something isn't working for you!
- Jackofall
Thank you for the feedback. It makes sense to unpivot and that I had to add the dimension right / wrong. From there on I will follow your instructions.
I get so frustrated by DAX and seem to lock up. Modeling an ordinary DB is no problems but I lack experience in modeling for BI. Those two facts makes this an excellent learning experience.