Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RichaBansal
Frequent Visitor

Categorize multiple columns to use it as a slicer

RichaBansal_0-1738105336424.png

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

4 REPLIES 4
suparnababu8
Super User
Super User

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
Super User
Super User

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!

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.

 

RichaBansal_0-1738161855551.png

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.

 

RichaBansal_1-1738162293723.png

 

Many thanks in advance

/R

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.