cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Sum by category with conditions

Hi,

I have a simple set of data 'TEST' as per below and I created an extra column on the status of the delivery.

The status is 1 when the tolerance is more than 3% above or below the ordered qty and 2 when within the tolerance.

'In Full Status' calculated column looks like this:

In Full Status = if(OR(TEST[Delivered qty]>TEST[Ordered qty]*1.03,TEST[Delivered qty]<TEST[Ordered qty]/1.03),1,2)
This will show that the first two rows of below table get a 1, the rest gets a 2.

 

Now  I look for a measure that needs to show that the sum of each status per order ID = zero when minimal 1 row = 1.  And when its relevancy is not relevant its status should be not summed. And when all rows for the ID are a 2, then show a 1 as total result sum for the order ID

 

So as soon as one row of the order ID is a 1, then the sum of that order ID status should be zero. Only when all rows are 2, the sum for the order ID can show 1.

As result order ID 562 status will need to be summed to show zero

Order ID 563 status will be not relevant and not shown results for

Order ID 564 status will be summed to show as result 1

 

Had many attempts with keepfilters, calculate, and you name it but I can not seem to get it to work.

Thanks for any help

 

Table headers:

Relevancy 

order ID

Product

Ordered qty

Delivered qty

 

 

Relevancy order ID Product Ordered qty Delivered qty

Relevant562A2523
Relevant562B2015
Relevant562C2020
Not Relevant563X2020
Not Relevant563C2525
Relevant564X3030
Relevant564C4039
Relevant564B5050
Relevant564A6061

 

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@DBNN 

Please try the following: 

Measure1 = 

VAR _STATUS = VALUES(TEST[In Full Status] )
RETURN
IF(
    MAX(TEST[Relevancy]) = "Relevant", 
    SWITCH(
        TRUE(),       
        1 IN _STATUS , 0 ,
        CALCULATE( COUNTROWS( _STATUS ) , TEST[In Full Status] = 2 ) = COUNTROWS( _STATUS ), 1 ,
        BLANK() 
    ),
    BLANK()
)

 

Fowmy_0-1606566535545.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

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

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

1 REPLY 1
Super User IV
Super User IV

@DBNN 

Please try the following: 

Measure1 = 

VAR _STATUS = VALUES(TEST[In Full Status] )
RETURN
IF(
    MAX(TEST[Relevancy]) = "Relevant", 
    SWITCH(
        TRUE(),       
        1 IN _STATUS , 0 ,
        CALCULATE( COUNTROWS( _STATUS ) , TEST[In Full Status] = 2 ) = COUNTROWS( _STATUS ), 1 ,
        BLANK() 
    ),
    BLANK()
)

 

Fowmy_0-1606566535545.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

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

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.