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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
graemejohnson
Helper II
Helper II

Calculating a percentage of filtered records VS overall records

Hi, this is my first posting, and I've only been using PowerBI for a week, so please go easy on me....

 

My scenario is that I'm attempting to report on Software deployments (aka Changes).

My data model is very simple

  • I have a table named 'Change' with a row per change
  • A change can be attributed to a project (as shown by the slicer in the image below)
  • A change can have a Status of 'Change Succeeded' or 'Change Failed'

 

One of my objectives is to calculate the percentage of Changes that have a status of 'Change Failed' - so for example, with the data shown in the screen grab below it would expect 3.41% to be shown i.e. (Failures/Change Count)*100 or  (4/117)*100.

 

In reference to the image below with the numbers in red circles...

(1) is a count of changes for the selected project(s) - all good

(2) is a count of changes for the selected projects, with a visual level filter on 'Status Name' to only count rows where it's 'Change Failed' - all good

(3) is a percentage of changes for the project(s)  and is a measure defined as

Change % = (Change[Change Count]/[Count All Rows])*100

(4) is what I was hoping was going to show 3.41% - but it's showing 0.33%.

 

I've a feeling that what's missing is a CALCULATED measure, but I'm at a loss where to start - please can someone give some advice?

 

2019-06-21_17h35_29.png

These are the visual filters I am applying to (4)

2019-06-21_17h36_39.png

 

Many thanks

 

Graeme

2 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

The 0.33 comes from the Percentage Change figure(9.79) x 4/117. It's a percentage of a percentage.

 

One way to get what you want I think is :

 

Count Failed % = CALCULATE(COUNTROWS(Change), Change[Status] = "Change Failed") / COUNTROWS(Change)

View solution in original post

Thank you so much @HotChilli .   That worked for me.

 

I genuinely appreciate the assistance - thanks once again!

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

The 0.33 comes from the Percentage Change figure(9.79) x 4/117. It's a percentage of a percentage.

 

One way to get what you want I think is :

 

Count Failed % = CALCULATE(COUNTROWS(Change), Change[Status] = "Change Failed") / COUNTROWS(Change)

Thank you so much @HotChilli .   That worked for me.

 

I genuinely appreciate the assistance - thanks once again!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors