Power BI allows us to group two or more items into a single group of items, that means you can combine two, or more than two items together into single group and display them as a single item on visuals.
It is very useful when you want to compare the combined total of specific items with other items.
Here we have a sample dataset named ItemList of Items as you can see below.
Lets start with displaying a total sold quantity by items, Lets create a one table visual and one chart visual for this.
Drag a ItemList table fields into table visual, also create a clustered bar chart for same as shown below.
Lets understand the requirement where you can use Groups, Suppose you have a requirement to analysis the total sold quantity of items in a specific categories (Software, Hardware, and Electronics), also analysis the combined sales of software, and hardware items with Electronics items.
Such kind of analysis can be done easily through by grouping the items into a specific groups accordingly.
Lets observe the item and breakdown them into category.
Items those are listed in Software category will be – Windows 10, Windows 8, and Antivirus Norton Antivirus.
Items those are listed in Hardware category will be – Mouse, Keyboard, Motherboard, Pendrive, and Graphics Card.
Finally items those are listed in Electronics category will be – Fridge, Fan, Philips Bulb, Tubelight, and Ac.
Lets create a grouping of items.
Go to Field Pane, Click on triple dots (…) in Item field under ItemList table or you can simply right click on Item Field.
Once you click on dots (…) a List of context menu opens, Just click on New Group
Once you click on New Group, a Groups window opens.
It displays the list of all the unique items available in you ItemList table.
Now you can provide a Group name or can use default name.
Next, we create a group for software items, so select all software items from the Ungrouped values lists given on the left side of window.
As you are selecting multiple items so press ctrl key for multiple selection of items.
Once you select all the software items then click on Group button.
Once you click on Group button, it combines all the selected software items into single group that you can see on the right side of window under Groups and members lists.
You can rename the Groups, by default it is named as an item that it holds.
As you have selected the Software Items so lets renamed it as software.
In similar way, you can create a Hardware group by selecting all hardware items into a single group, and a Electronic Group for all electronics items.
As you can see after creating a Hardware and Electronics Group, Now you have divide all of your items into three groups.
Once you create a group for all items, you will notice the lists of ungrouped values on the left side of window is now blank as there is no item available.
Now click on OK button.
Once you click on Ok Button a Group of items is created successfully, that can be seen in field pane.
Lets drag the Group field Item (Groups) into visuals. As you can see, now it displays a total sum of sold quantity of items into a single group for each groups that you have created.
Now you can easily compare the total sold quantity of items in terms of category of items.
Lets combine the Hardware and Software items into single groups called as HardwareSoftware and compare the total combined quantity of them with Total quantity of Electronics items.
For this you have to edit groups to make some changes as given below.
Edit an Existing Group
Go to field pane, right click on an existing Group Item(groups), or click on triple dots(…)
A list of context menu is opens , then Click On Edit Groups
Once you click on Edit Groups, a Group windows opens which displays an existing group that you have created.
This time you have to combined a Hardware and Software items together into single a Group. So first you have to ungroups the items from one of them group.
Lets say, you ungroup the items from Software group, then you need to select software group from Groups and members lists on the right side of window then click on Ungroup button.
Once you click on Ungroup button, all the items which were tagged with Software Groups is moved back to ungrouped values lists on the left side.
Now time is to add back these ungrouped items to Hardware group.
Just select all these items one by one along with pressing a Ctrl key, once you manage to select all these items together then click on hardware Group given on the right side of window , and click on Group button. By doing so you have added software items with hardware items.
Once you follow the above step, you see, all the Software items are moved under Hardware Group.
Now you can renamed it as HardwareSoftware as it combines the all items of Hardware and Software.
After renaming it, just click on OK button to make the final changes.
Once you click on OK button, you can see now visual is showing items related Hardware and software into single item.
Now you can easily analysis the Total combined quantity sold for hardware and software Items with Electronic items.
Also Read..
Implementation of Dynamic Page Navigation in Power Bi
Create Index column using a Power Query
1,954 total views, 1 views today