Forum Discussion
Helper IGrouping data to display at different levels
Afternoon all,
I've been grappling with a better way to display and interact with some data and I'm sure Power BI can do it but it's been so long since I've used it I am really stuck with how to do it.
I have a table that shows all the vehicles we use at work and where we use them. I can't share the raw data as it's restricted but I have created a dummy set of data that hopefully makes sense.
Essentially there are 3 columns at the start that show which part of the organisation that we're looking at. The rest of the columns show a vehicle type. Therefore each row details a location and how many of each variant of vehicle that part of the organisation has. One nuance is that the vehicles are grouped, in the dummy data you have Cars, Trucks and Lorries.
I'd like a way to quickly see how many of each vehicle type are in what location (i.e. car, truck, lorry) but also to look at each vehicle type and see where they are. In addition, I'd like to be able to drill down into those vehicle types (i.e. truck 2wd, 4wd etc).
I think I need to create a heirachy and then displaying the data should be relatively simple, but I'm struggling to get started at the moment. Any help would be much appreciated.
Hi! I added the data into Power BI, then went into Power Query to unpivot most of the columns. I selected Organisation, Dept, and Loc then went to Transform ribbon and clicked the down arrow on 'Unpivot Columns' and selected 'Unipivot Other Columns'. This step puts the data into this format:
You can name your columns that it created anything you want, I just left mine the default names for this exercise.
Next, I split the Attribute column by delimeter with space as the delimeter and chose left most occurance:
Now, we have data like this:
Next, I click close and apply.
Next, I created a measure for vehicle count as Vehicle Count = SUM ('YourTable'[Value])
Now, you can create visuals and filters however you want:
2 Replies
- audreygerred
Super User
Hi! I added the data into Power BI, then went into Power Query to unpivot most of the columns. I selected Organisation, Dept, and Loc then went to Transform ribbon and clicked the down arrow on 'Unpivot Columns' and selected 'Unipivot Other Columns'. This step puts the data into this format:
You can name your columns that it created anything you want, I just left mine the default names for this exercise.
Next, I split the Attribute column by delimeter with space as the delimeter and chose left most occurance:
Now, we have data like this:
Next, I click close and apply.
Next, I created a measure for vehicle count as Vehicle Count = SUM ('YourTable'[Value])
Now, you can create visuals and filters however you want:
mnt23Thanks! The unpivot step was what I was really struggling with.
I appreciate the help!