Forum Discussion
Create visualization wherein sub categories in each column total can be shown under a Primary one
Hello Experts,
I am very new to Power BI, so please bear with me if I am slow on catching up on the solutions you all share.
I am looking to have the visualization wherein I can choose any one of the Primary Category and their Sub Category data is shown in the form of a chart. I know I can use the DAX and Calculate.. but the number of Primary and the sub category shown in the sample is just the tip of the iceberg. there is a possible combination of 399 so just was hoping if there was an easier route than to create additional 399 columns.
below pasted is a sample for quick reference.
Thank you so much in advance for the help!!
| Date | Segment | Category 1 | Category 2 | Category 3 | Category 4 | Category 5 | Category 1.Sub Category 1 | Category 1.Sub Category 2 | Category 1.Sub Category 3 | Category 1.Sub Category 4 | Category 1.Sub Category 5 | Category 2.Sub Category 1 | Category 2.Sub Category 2 | Category 2.Sub Category 3 | Category 2.Sub Category 4 | Category 2.Sub Category 5 | Category 3.Category 3.Sub Category 1 | Category 3.Category 3.Sub Category 2 | Category 3.Category 3.Sub Category 3 | Category 3.Category 3.Sub Category 4 | Category 3.Category 3.Sub Category 5 | Category 4.Sub Category 1 | Category 4.Sub Category 2 | Category 4.Sub Category 3 | Category 4.Sub Category 4 | Category 4.Sub Category 5 | Category 5.Sub Category 1 | Category 5.Sub Category 2 | Category 5.Sub Category 3 | Category 5.Sub Category 4 | Category 5.Sub Category 5 |
| 12/1/2022 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 12/1/2022 | 2 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 0 |
| 12/1/2022 | 3 | 0 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 1 |
| 12/1/2022 | 4 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 1 | 1 |
| 12/1/2022 | 5 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 1 | 1 | 0 |
| 12/1/2022 | 6 | 1 | 0 | 1 | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 0 |
| 12/1/2022 | 7 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 |
| 12/1/2022 | 8 | 0 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 |
| 12/1/2022 | 9 | 0 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 0 | 1 | 1 | 0 | 0 |
| 12/1/2022 | 10 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | 0 | 0 |
| 12/2/2022 | 11 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 1 |
| 12/2/2022 | 12 | 1 | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 |
| 12/2/2022 | 13 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 |
2 Replies
Anonymous , You have to create a new column. Or Group. (Both will create a new column)
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning
Hello amitchandak,
First off, i really appreciate you taking the time off your schedule and sharing the response.
Well, the solution you suggested is when the group data is in rows. However, in my case my data to be grouped is is column. Moreover, for each category and sub category there is a value 0 or 1 is assigned. So how I wanted my groups to work is when I select a Primary category, it should first look only for those calls where it is 1 and ignore 0s. Then within the ones, when I select a Sub Category it should give a sum of calls where both Primary and sub category is 1.
I hope I am able to explain my query this time.
Thanks in advance!!
