Categorize multiple columns to use it as a slicer

[RichaBansal]

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

[suparnababu8]

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!

[suparnababu8]

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!

[RichaBansal]

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?

[RichaBansal]

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