Forum Discussion

renmello's avatar
renmello
Icon for Helper I rankHelper I
6 years ago
Solved

Count percentage values in measures

Hello, everyone!

 

I would like to count the number of values in a measure that is under 30%, the number of values in the same measure between 30% and 50% and the number of values that are above 80%.  The measure with the percentage values is: 

 

% Executada =
    if(sum(Projetos[Orçamento DIretoria])=0,Blank(),DIVIDE(SUM('Tarefas'[Realizado]),SUM(Tarefas[Custo Atual]),0))
 
It returns this:
 
I've tried COUNTA, COUNTAX with filters, if, calculate, and it always returns 1, as the card above the table shows for the percentages below 30%. What am i missing in my knowledge? 

Thanks for the help!
perce
  • renmello's avatar
    renmello
    6 years ago

    I found out what i was missing!

    I had a table for the measures and a table for the projects. My measure for countx was using the table for the measures instead of the projects table. I was using this measure: 

    Qtde <30 = Countax(Filter('Medidas Financeiras','Medidas Financeiras'[% Executada Cronograma]<0.3),'Medidas Financeiras'[% Executada Cronograma])

    The right one is: 
     
    Qtde <30 = Countax(Filter('Projetos','Medidas Financeiras'[% Executada Cronograma]<0.3),'Medidas Financeiras'[% Executada Cronograma])
     
    Fixing this i was able to use countax with filter. 

    After that i wrote more measures to count between values: 

    Qtde entre 30 e 50 = Countax(Filter(projetos,'Medidas Financeiras'[% Executada Cronograma]>=.3 && 'Medidas Financeiras'[% Executada Cronograma]<=0.5),'Medidas Financeiras'[% Executada Cronograma])

    Qtde entre 50 e 80 = Countax(Filter(projetos,'Medidas Financeiras'[% Executada Cronograma]>=.5 && 'Medidas Financeiras'[% Executada Cronograma]<=0.8),'Medidas Financeiras'[% Executada Cronograma])

    Qtde Maior que 80 = Countax(Filter(projetos,'Medidas Financeiras'[% Executada Cronograma]>0.8),'Medidas Financeiras'[% Executada Cronograma])
     
    Is there a smarter way of counting this?

    Thanks for the answer!

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    renmello , what you wany count. Assume you want to count a column group, then you will do like

     

    countx(summarize(table,table[group], "_1",[% Executada]),[_1]>.3 && [_1]<.5)

    • renmello's avatar
      renmello
      Icon for Helper I rankHelper I
      6 years ago

      Thanks for the answer. 

       

      It looks like the function countx cannot work with values of type boolean.

       


      I would like know how many values of the column i created with the measure are <30%, how many are between 30 and 50% and how many are above 80%

  • renmello's avatar
    renmello
    Icon for Helper I rankHelper I
    6 years ago

    I found out what i was missing!

    I had a table for the measures and a table for the projects. My measure for countx was using the table for the measures instead of the projects table. I was using this measure: 

    Qtde <30 = Countax(Filter('Medidas Financeiras','Medidas Financeiras'[% Executada Cronograma]<0.3),'Medidas Financeiras'[% Executada Cronograma])

    The right one is: 
     
    Qtde <30 = Countax(Filter('Projetos','Medidas Financeiras'[% Executada Cronograma]<0.3),'Medidas Financeiras'[% Executada Cronograma])
     
    Fixing this i was able to use countax with filter. 

    After that i wrote more measures to count between values: 

    Qtde entre 30 e 50 = Countax(Filter(projetos,'Medidas Financeiras'[% Executada Cronograma]>=.3 && 'Medidas Financeiras'[% Executada Cronograma]<=0.5),'Medidas Financeiras'[% Executada Cronograma])

    Qtde entre 50 e 80 = Countax(Filter(projetos,'Medidas Financeiras'[% Executada Cronograma]>=.5 && 'Medidas Financeiras'[% Executada Cronograma]<=0.8),'Medidas Financeiras'[% Executada Cronograma])

    Qtde Maior que 80 = Countax(Filter(projetos,'Medidas Financeiras'[% Executada Cronograma]>0.8),'Medidas Financeiras'[% Executada Cronograma])
     
    Is there a smarter way of counting this?

    Thanks for the answer!