How to calculate percentage by each group

[biswad]

Hi all, 

I have a dataset which consists of the data as shown on the below table "Table -1" , however, I would like to show the data in the power bi dashboard as shown on "Table - 2" where each role_Status were calculated for each Region in percentage wise. I have tried to add measure column for each role status and used if query to count as 1 if there is value in the Role_status or else show blank, then use the sum function to count all the 1s on each role_status and then use  matrix visualization and use role_status on value but I'm unable to represent this data as per table 2 . Any help on this would be greatly appriciated. 

Table -1

[lbendlin · Accepted solution]

You can do this without code. Put your data into a matrix visual and show values as percentage of column total.

[v-yiruan-msft · Accepted solution]

Hi @biswad ,

You can create a measure as below to get it, please find the details in the attachment.

Percentage = 
VAR _region =
    SELECTEDVALUE ( 'Table'[Region] )
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Brand] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Region] = _region )
    )
VAR _bcount =
    COUNT ( 'Table'[Brand] )
RETURN
    DIVIDE ( _bcount, _count )

Best Regards

[biswad]

@v-yiruan-msft  that is the perfect solution to my problem, lots of thing to learn, much appriciated you help. I will accept as a solution friend. 

[lbendlin · Accepted solution]

You can do this without code. Put your data into a matrix visual and show values as percentage of column total.