Forum Discussion
Categorize multiple columns to use it as a slicer
Super UserHi 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!
Hi suparnababu8
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