Forum Discussion
Problems 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!
I created the Measures table using the "Enter Data" option in the ribbon on the Home tab.
1) once the "Enter Data" window opens up, just change its name (no need to add any values or content). Close the window
2) a new table is created under the Fields panel on the right of the screen. You will see that a column is created by default, but just ignore it for a second.
3) create a new measure within this table (or bring one in from another table).
4) Once the new table has a measure, you can delete the default column which was included when you created the table. You can delete this column by selecting the ellipsis on the right of the column name.
5) once you have deleted the default column, minimise the Field panel by clicking on the arrow ( " > " ) next to the Field header name. Now click on the expand arrow (" < " ) and voliá! There is your measure table, with its corresponding measure Icon and placed at the top of your tables list.
This is very useful to keep your Fields content tidy: measures on top, tables below.
If your "Value1" field in the table you created is empty, follow the steps from 4) onwards and you should get the measure table configured.
Does it work?
So far so good. Thank you for spending your precious time on me. I picked up a lot of things from you.