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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors