Exclude Zero Values from Count Value Field Setting

[kylec23]

Hi all,

I have a field in my data model "Haul Price", which is giving me the price we charged the customer strictly for hauling. I am trying to use that field to summarize a load count, that way upper management can see how much we've charged versus how many loads we have delivered.

Here's the issue: we deliver and also allow customers to pick up. So I have plenty of rows with zero value in Haul Price.

My goal: to use the Haul Price field in my data model to calculate how many rows have a haul price in them > 0 on my PivotChart. All charts are generated from the data model.

Thank you in advance!!

 

[RoryA]

You'll have to create a measure for that, such as =COUNTROWS(filter(Table1,Table1[Number]>0))

 

[kylec23]

You'll have to create a measure for that, such as =COUNTROWS(filter(Table1,Table1[Number]>0))

This is the measure I created: =COUNTROWS(FILTER('JWS Ticket History','JWS Ticket History'[Haul Price]>0))

No errors in the formula. I can see excel running the calculation. It's just not updating my chart. Do I have to do something else besides create the measure?

Thanks for your reply

 

[RoryA]

I assume you added it to your pivot table?

 

[kylec23]

I assume you added it to your pivot table?

I have no pivot tables, only pivot charts which have been generated from the data model.

 

[RoryA]

Then all you need to do is drag it into the values area for the pivot chart in the field list.

 

[kylec23]

Then all you need to do is drag it into the values area for the pivot chart in the field list.

I gotta tell ya.... I feel like a dumb-dumb. I didn't see that it created that field value because it was at the bottom of the list, I had to scroll down to see it.

Your help is much appreciated!!

 

[RoryA]

It would be more intuitive to me if it added it to the pivot by default.