Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

needs % share in row wise

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%

  • 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

  • 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's avatar
    Anonymous
    7 years ago

    Great!!!!!!!!!

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion

    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's avatar
      Anonymous
      Not applicable

      Thanks, its working

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion

    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's avatar
      Anonymous
      Not applicable

      Great!!!!!!!!!