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
RodrigoTXRA
Helper I
Helper I

Dax formula - dealing with zero amounts

Hi All


Hope someone can shed some light on this formula below. Long story short, it works well as long as all the items contains some type of value, as soon as one of the items is ZERO, this formula fails to calculate.

 

PERFORMANCE KPI % = (
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE ACC REVIEWS] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE FEEDBACK/MI] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE WINS] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE QA] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE MI] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE STAKEHOLDER ENGAGEMENT])/5

 

Appreciate if someone can assist me with this query

 

Regards

Rodrigo

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Here it is...

 

PERFORMANCE WINS =IF( ISBLANK([SUM OF WINS]),0,[SUM OF WINS])/ IF( ISBLANK([SUM OF TARGET WINS]),0,[SUM OF TARGET WINS])  *100

 

I have replaced the blank by 0, however you can change with your desired value.

 

Thanks
Raj

View solution in original post

9 REPLIES 9
RodrigoTXRA
Helper I
Helper I

Thanks guys, this helps a lot.

 

Would you be able to assist me inserting an ISBLANK in this formula below?

 

PERFORMANCE WINS = [SUM OF WINS]/[SUM OF TARGET WINS]*100

 

I think that if we manage to insert a ISBLANK on this formula above, then I would be able to get the other formula to work..

Anonymous
Not applicable

Here it is...

 

PERFORMANCE WINS =IF( ISBLANK([SUM OF WINS]),0,[SUM OF WINS])/ IF( ISBLANK([SUM OF TARGET WINS]),0,[SUM OF TARGET WINS])  *100

 

I have replaced the blank by 0, however you can change with your desired value.

 

Thanks
Raj

Thanks Raj, I'm new to Power BI and you guys are legends

Thejeswar
Resident Rockstar
Resident Rockstar

@RodrigoTXRA,

Your DAX is fine.

 

But are you sure, this is happening when one of the values is 0.

I tried the same and find below the result I got

values.PNG

 

Also "Fails to Calculate means" you mean it is returning NULL Values or is it giving an error?

 

If it is giving NULL Value, then your items should be having NULL as their value and not Zero.

 

Regards,

Thejeswar

 

 

Anonymous
Not applicable

Exactly. Your formula is straight forward and no issues with that. Any issues should be with your data and it may have NULL . Try using Isblank (value,0,value) if you have blank. Similarly handle NULL as well.

 

Thanks

Raj

@Thejeswar Thanks so much for the effort in analysing this query.

I believe the issue is when the data is NULL - see below and appreciate your thoughts on this

 

Dax issue.PNG

@RodrigoTXRA,

Any tool when it has NULLs in the calculation, returns NULL. Because NULLs cannot be  equated to any value automatically

 

So in this case replace NULLs with Zero wherever needed.

That should solve the issue!!!

 

 

RodrigoTXRA
Helper I
Helper I

Hi All

 

Just wondering whether someone can help me. 

I have this formula below which divides de KPIs by 5, but whenever one of the KPI is empty or have ZERO as the value, this formula fails to calculate properly.

 

PERFORMANCE KPI % = (
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE ACC REVIEWS] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE FEEDBACK/MI] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE WINS] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE QA] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE MI] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE STAKEHOLDER ENGAGEMENT])/5

Appreciate any kind of assistance

Regards

Rodrigo

I cannot replicate the issue - for me 0/null values are aggregated properly in the calculated column
are you sure there are no text values in the empty cells?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

Top Solution Authors