Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello experts,
I have a strange question. My data looks like above. I have 15 different categories and each category have same 4 options. All options can either have value of 0 or 1. I want to visualize the data like this : slicer of categories and when i choose category A, it should show the % of 1,2,3,4 of total rows.
Is that even possible?
I dont want to pivot as then I would have 60 rows for one row and my data adds 100 rows every week.
Many thanks
/Richa
Hi @RichaBansal
Ohh..! I thought that it's in two tables. Then you can share your table name with Sample data having all columns and expected output in the form of snapshot. It will help us to fix your issue.
Thanks!
Hi @RichaBansal
Try with below measure
SelectedOptions =
VAR SelectedCategories = VALUES('CategoryTable'[Category])
RETURN
DIVIDE(
SUMX(
SelectedCategories,
SWITCH(
'CategoryTable'[Category],
"Category A", SUM('CategoryTable'[Category A - 1]),
"Category B", SUM('CategoryTable'[Category B - 1]),
"Category A", SUM('CategoryTable'[Category A - 2]),
"Category B", SUM('CategoryTable'[Category B - 2]),
"Category A", SUM('CategoryTable'[Category A - 3]),
"Category B", SUM('CategoryTable'[Category B - 3]),
"Category A", SUM('CategoryTable'[Category A - 4]),
"Category B", SUM('CategoryTable'[Category B - 4]),
0
)
),
COUNTROWS('CategoryTable'),
0
)
Let me know if it works
Thank you!
Thank you for the response.
I didnt quite understand what CategoryTable & category represents. All of my data is in one table. Even if i create a separate table to have a category column, i will have no common column to create relationship.
Makes sense or am i missing something here?
Here is a snapshot (there are other columns as well)
There are 15 categories, each category have same 4 options. If the person chose an option for a category the value is 1 else it is 0. One person can choose more than 1 option in a category, so the values can be more than 100%. One person have to choose atleast one option in all the categories.
As mentioned earlier, i dont want to pivot the table as each row will result into 60 rows and my data adds 100 rows every week.
I have a date table to drill up and drill down. I also use gender and city filter in my graphs.
I want to have a category slicer to select the category and show its respective count/percentage of options 1,2,3,4. So if I choose A, it should show the below graph.
Many thanks in advance
/R
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
68 | |
60 | |
51 | |
36 | |
36 |
User | Count |
---|---|
83 | |
72 | |
58 | |
45 | |
44 |