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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Nielf
Helper I
Helper I

Only calculate measure for rows with data

I have simple table in Power BI, where I have created a measure (% Compliance error percentage) as Total error count / open opportunities:

% Compliance error percentage = 

DIVIDE(SUM('Data compliance'[Total error count]),
sum('Data compliance'[Open opportunities]),
0
)

 

The data is logged every two weeks (week 1, 3, 5 and so forth as per the table below).

 

Nielf_0-1642419748487.png

 

This simple calculation works fine. However, instead of showing the 'error percentage' I would like to show the 'valid percentage' by subtracting the error percentage from 1. E.g., 'valid percentage' for week 1 will be 93,75%.   

 

The issue is that if I make a simple measure like this:

% Compliance valid percentage = 

1 - [% Compliance error percentage]


I will end up with values for all dates, also the ones where we haven't logged any data, since 1-0 will return a value. 

 

Nielf_2-1642420601172.png

 

My question is now - is there a way I can limit the measure to only make the calculation for the rows that have data?

 

I have tried with varies IF statements but I haven't found a solution,. 

1 ACCEPTED SOLUTION
mh2587
Solution Sage
Solution Sage

https://www.burningsuit.co.uk/how-dax-treats-nulls-zeros/

Follow this url how to handle null and zeros value in dax



Muhammad Hasnain

#LetsConnect https://www.linkedin.com/in/hasnain2587/

View solution in original post

4 REPLIES 4
Nielf
Helper I
Helper I

Thanks for the link @mh2587. It explained the differences nicely and I produced a solution using ISBLANK().

 

IF(NOT(ISBLANK([% Compliance error percentage])), 1-[% Compliance error percentage])

 

mh2587
Solution Sage
Solution Sage

https://www.burningsuit.co.uk/how-dax-treats-nulls-zeros/

Follow this url how to handle null and zeros value in dax



Muhammad Hasnain

#LetsConnect https://www.linkedin.com/in/hasnain2587/
Nielf
Helper I
Helper I

@mh2587 yes - that is working. However, is it possble to return 100% as 'valid percentage' if 'error percentage is 0?

 

Nielf_0-1642421800946.png

 

mh2587
Solution Sage
Solution Sage

 if([% Compliance error percentage] <> Blank(),1 - [% Compliance error percentage])


Muhammad Hasnain

#LetsConnect https://www.linkedin.com/in/hasnain2587/

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.