Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
renmello
Helper I
Helper I

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:Power BI.jpg
 
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!
1 ACCEPTED SOLUTION
renmello
Helper I
Helper I

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!

View solution in original post

3 REPLIES 3
renmello
Helper I
Helper I

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!
amitchandak
Super User
Super User

@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)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thanks for the answer. 

 

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

 

renmello_0-1597953684053.png


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%

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.