Sum if two colomn values are equal

[ThomasWW]

Hi!

I'm trying to sum the cases where to two values are equal at a lower level of detail. As you can see below, I have a data set containing a category, sub-category, product, status, shipment date column. I have created a column called "Shipped" that is 1 if Status is Shipped and Date of Shipment is not empty (DAX: 

Shipped = IF(AND(RELATED('Sub-Category_Table'[Status])="Shipped", NOT(ISBLANK('Product_Table'[Date of Shipment]))), 1, 0).

If I create a Table Visualisation in PBI (2nd box below), I can sum the Shipped column and simply count the number of orders within each Sub-Category. I would like to create a measure or column (??? in the screenshot below) that is 1 when the sum of Shipped and Count of Order is equal on the sub-category level and otherwise 0. Ultimately, I want to summarize that number across categories or within a single category (I simply do it with a Card and Slicer right now).

I have no idea how to make the ???-measure/column. Can anyone help?

The datamodel is relatively simply. Category is top-layer, Sub-Category middle-layer and Orders are the lowest layer. One-to-Many relationship between Category and Sub-Category, and One-to-Many between Sub-Category and Order. 

Thanks, 

Thomas

[V-pazhen-msft · Accepted solution]

@ThomasWW 

I guess you do not need related or additional conditions. I have used your first table as sample and create the following measure:

Measure = IF(SUM([Shiped])=COUNT([Order]),1,0)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[lbendlin]

The problem is not only your ???-measure - you need separate measures, one for the subcategory and one for the category, as they have different mathematical rules.

first measure is count(shipped)=sum (count of order)   (crazy, eh?)

second measure actually needs an iterator. For the category you need to list all subcategories, collect their measures, and then add that up.