Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I bring in our crew plan to power bi for reporting. What I do is extract the totals lines (bold) in the screen shot to another tab then use that to import into Power BI.
Management now want to be able to drill into the headings to see the subtotals underneath.
My issue is the way the data is structured nothing really binds any of the sub headings to the main headings, when it boils down to it is just one column of information but I pull out the data I need first.
Are there any suggesitons on how to handle this sort of structure where I need to show on a graph the weekly numbers for all the main headings (Production, Assets) in this example, but then when I click into Assets it would show me a graph with all the individual roles also?
I agree with @Ashish_Mathur in using Power Query and unpivot. I think your ideal data structure should probably look something like this (abbreviated):
Cagtegory | Role | Week | Value |
Production | PROD | 3-Jan | 8 |
Production | PRODMAN | 3-Jan | 2 |
Assets | MODSUPE | 3-Jan | 2 |
Assets | MOD | 3-Jan | 15 |
In general, I would also recommend that you do some research on data modeling for Power BI. The structure you've displayed in your screenshot is really only useful for displaying a report, but not at all for storing or modeling data.
Yeh the data isn't laid out in the best way, I have now say in how it is presented, just what comes out to people in the business who use it.
I suppose the underlying format is always the same, I suppose if I could get a category column in there somehow and populate with the data I need I could then use that to drill down.
Hi,
In the Query Editor, you should get rid of the subtotal rows. Right click on the first column and then select "Unpivot other columns". This will be a good start.
I want to keep all the subtotal rows. I want to be able to see the production line for example and see the line from week to week. But then I want to be able to click on production and drill down into it to see what the individual lines are that make up the 34 for Jan 24th for example.
You can always see the subtotal row in the visualisation - you should just not have that row in the dataset.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |