needs % share in row wise

[Anonymous]

Hi

My data table looks like

id    status

1       a

2         a

3         a

4         a

5          a

6           a

7           b

8            b

9              c

10             c

I want to display below table in the report. Can u help me resolve this

status    # Count   %share

    a               5            50%

     b              2            20%

      c              2            20%

[AlB · Accepted solution]

Hi @Anonymous 

Try this, where Table1 is the name of the first table you show:

1. Place Table1[status] in the rows of a matrix visual

2.  Create these two measures and place them in values of the matrix visual>Note the second measure uses the first one

MCount = COUNT(Table1[id])

MP%share = DIVIDE([MCount], CALCULATE([MCount], ALL(Table1)))

3.  Select [MP%share]  to be shown as percentage if required

[nandukrishnavs · Accepted solution]

1st Method

Assuming that your table name is "Table1".

create below DAX measure for the count.

#Count = 
CALCULATE ( COUNT ( Table1[Id] ) )

below DAX measure for the percentage.

%share = 
VAR c = [#Count]
VAR totalcount =
    CALCULATE ( COUNT ( Table1[Id] ), ALL ( Table1 ) )
VAR result = ( c / totalcount )
RETURN
    result

choose the table visual then add [status] column and both measures.

Change the format of "%share" as % in the modeling tab.

2nd Method

1. Choose table visual then add [status] column.
2. again add [status] column and change its representation as count.
3. repeat 2nd step and show value as  "percent of grant total"

You can rename the column headers if required. 

[nandukrishnavs · Accepted solution]

1st Method

Assuming that your table name is "Table1".

create below DAX measure for the count.

#Count = 
CALCULATE ( COUNT ( Table1[Id] ) )

below DAX measure for the percentage.

%share = 
VAR c = [#Count]
VAR totalcount =
    CALCULATE ( COUNT ( Table1[Id] ), ALL ( Table1 ) )
VAR result = ( c / totalcount )
RETURN
    result

choose the table visual then add [status] column and both measures.

Change the format of "%share" as % in the modeling tab.

2nd Method

1. Choose table visual then add [status] column.
2. again add [status] column and change its representation as count.
3. repeat 2nd step and show value as  "percent of grant total"

You can rename the column headers if required. 

[Anonymous · Accepted solution]

Great!!!!!!!!!

[AlB · Accepted solution]

Hi @Anonymous 

Try this, where Table1 is the name of the first table you show:

1. Place Table1[status] in the rows of a matrix visual

2.  Create these two measures and place them in values of the matrix visual>Note the second measure uses the first one

MCount = COUNT(Table1[id])

MP%share = DIVIDE([MCount], CALCULATE([MCount], ALL(Table1)))

3.  Select [MP%share]  to be shown as percentage if required

[Anonymous]

Thanks, its working