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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DBNN
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
Fowmy
Super User
Super User

@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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

1 REPLY 1
Fowmy
Super User
Super User

@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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors