Forum Discussion

Warner's avatar
Warner
New Member
5 years ago
Solved

Column Count for total and %

Hi All, 

 

I have 2 columns within a table (see below). 

 

What i am trying to do it get the Top % total of either M or F in a card visual for example (M = 51%). 

 

I am also trying to do the same for Ethnicity. So counting the total number of each ethnicity and then displaying the top ethnicity within a card as a %? 

 

 

 

Thanks in Advance. 

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi Warner ，

    Try the measure like below:

    TEST = 
VAR TESTALL =
    CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) )
VAR COUNT1 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Client Gender] = SELECTEDVALUE ( 'Table'[Client Gender] )
        )
    )
VAR ALL1 =
    DIVIDE ( COUNT1, TESTALL, 4 )
RETURN
    FORMAT ( ALL1, "0%" )

    Since I do not have your specific data, I have created a sample,return:

     

     

     And you could do the same for Ethnicity.

     

     

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Warner , Generic measure with visual level filter 

     

    divide(countrows(Table), calculate(countrows, all(Table)))

    or specific filter measure example 
    divide(countrows(filter(Table, Table[Gender] ="M")), calculate(countrows, all(Table)))

    divide(countrows(filter(Table, Table[Gender] ="M")), calculate(countrows, allselected(Table)))

    • Warner's avatar
      Warner
      New Member
      5 years ago

      Hi, 

       

      I have tried what you have suggested but i cannot seem to get it to work. 

       

      There are 314 records within the Gender column, within that i know where are 171 "M" records. But I would like the card to just say 54% "M" which is the total "M" within that column. 

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi Warner ，

    Try the measure like below:

    TEST = 
VAR TESTALL =
    CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) )
VAR COUNT1 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Client Gender] = SELECTEDVALUE ( 'Table'[Client Gender] )
        )
    )
VAR ALL1 =
    DIVIDE ( COUNT1, TESTALL, 4 )
RETURN
    FORMAT ( ALL1, "0%" )

    Since I do not have your specific data, I have created a sample,return:

     

     

     And you could do the same for Ethnicity.

     

     

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien