Forum Discussion
Problems modelling Excelfile for use in PBI
I have an Excel where I already took aggregated totals per sample date. I look at logs and divide the results in 7 columns then I add it up per day.
Excel dataRight wrong groupedSub categories
ABC = Right, DEFG = Wrong. In PBI I added a separate Table for Dates so I can make my own flavors of slicing. I have tried to use one unified table, two different for right / wrong separated and Hierarchies.
I am too stupid to figure out how to get a Pie chart that allows me to click on right 1685 (85,3%) and get a drill down to A 1426 B 185 C 74. My blood pressure is skyrocketing. I would have solved this with SQL / Report builder fast enough. Any help would be most welcome.
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!
11 Replies
PLease provide a sample of your data, data structure and model structure
Period 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.
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!
