Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
77 | |
76 | |
44 | |
30 | |
26 |
User | Count |
---|---|
97 | |
91 | |
52 | |
47 | |
46 |