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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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 III
Resolver III

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.