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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tuliosouzaa
Frequent Visitor

Calculate sum of positive values and divide by total

I created a measure (Delivery Hours Difference) by subtracting the values of two columns:

tuliosouzaa_0-1709132682065.png

Now I need to get a card with a 'rate' value that is: The amount of positive rows in Delivery Hours Difference divided by the total amount of rows. How can I achieve this?

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

Hi @tuliosouzaa , You can try below DAX

 

I have used total row counts both positive and negative

 

Rate = DIVIDE( CALCULATE( COUNTROWS('YourTableName'), 'YourTableName'[Delivery Hours Difference] > 0 ), COUNTROWS('YourTableName') )

 

Please accept as solution and give kudos if it helps





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
bhanu_gautam
Super User
Super User

Hi @tuliosouzaa , You can try below DAX

 

I have used total row counts both positive and negative

 

Rate = DIVIDE( CALCULATE( COUNTROWS('YourTableName'), 'YourTableName'[Delivery Hours Difference] > 0 ), COUNTROWS('YourTableName') )

 

Please accept as solution and give kudos if it helps





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I see that makes sense and I understand the logic, but I get a ' function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.' error.

I fixed it editing the filter:

DIVIDE( CALCULATE( COUNTROWS('TableName'), FILTER(VALUES('TableName'), [Delivery Hours Difference] >0)),COUNTROWS('TableName') )

 

Thanks for the help!

Musadev
Resolver II
Resolver II

What do you mean by the total count of the columns?
you mean total occurrance of the positive values?

I did not express myself correctly. Tha would be the total amount of rows. I edited the original question.

Hi @tuliosouzaa 
As per the screenshot, this is the required result, right?

Diff/(total rows where diff is >0)

Musadev_0-1709136179831.png

 

 

 

The result I need it the count of positive rows divided by the count of all rows. In your example that would be 0.33, being 5 positive diff rows divided by 15 total rows.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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