cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Avivek
Post Patron
Post Patron

Need to modify the measure to not show infinity value

I had created a measure which was 

ASP 2 = [Sales Amount]/SUM(Sales[Item Qty])
But in this case if item qty(units) is 0 then value shows as Infinity or NaN. So i modified my measure as 
ASP = IF(SUM(Sales[Item Qty])=0,0,Sales[Sales Amount]/SUM(Sales[Item Qty]))
But there is a small issue with this solution,
Avivek_1-1618987613472.png

So whenever the item qty is 0 or no value is value then ASP appears as 0. But the user does not want to see those systems which has no values at all.

If we put a visual filter not to show ASP as 0 then all the records where maybe other values such as sales, units are there for the system but asp is 0 then those records are also not shown.

Is there any other way to get this result.

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @Avivek 

Please try the below.

 

newmeasure =
DIVIDE ( [Sales Amount], SUM ( Sales[Item Qty] ), blank())

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/

View solution in original post

1 REPLY 1
Jihwan_Kim
Community Champion
Community Champion

Hi, @Avivek 

Please try the below.

 

newmeasure =
DIVIDE ( [Sales Amount], SUM ( Sales[Item Qty] ), blank())

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors