Count values and group them by category (without the category!)

[eltonxavier]

Hello guys,

I have a table called Audits like this:

And the customer wants to see the audits grouped by category. The issue here is that we don't have the categories. The result table should look like:

It sounds simple but I have no idea how to do it. Any ideas, please?

[v-piga-msft · Accepted solution]

Hi @eltonxavier,

Please follow the steps below.

1. Go to Query Editor and Rename the column to Four Pillars and PPE;

2. Select the two columns Four Pillars and PPE and Unpivot columns.

3. Rename the Attribute to Category and Close and Apply.

4. Go to Modeling tab and create two measures with the formula below.

Safe = var temp= CALCULATE(COUNT(Table1[Value]),FILTER('Table1','Table1'[Value]="Yes")) return IF(ISBLANK(temp),0,temp)

UnSafe = var temp= CALCULATE(COUNT(Table1[Value]),FILTER('Table1','Table1'[Value]="No" )) return IF(ISBLANK(temp),0,temp)

5. Create the matrix like below.

More details, you could refer to the attachment.

Hope this can help you!

Best Regards,

Cherry

[eltonxavier]

@v-piga-msft you're a star! It just worked, simple as that! Thank you so much for your help 😃