Distinct count measure with filter

[legrand]

Hi,

I have a table with sales orderlines:

I have a related table via an item table with the manufacturer of each item (the item no is stored in the column "No")

I want to create a measure which counts the distinct values of the column "Document_No" (which stores the order number which has 1..n sales orderlines) if the following two criterias are met:

• A "Document_No" has 5 sales orderlines of a specific Manufacturer (let's call the manufacturer "super duper")
• A sales orderline of the manufacturer "super duper" has the "Line_Amount" = 0

If possible without creating another table which groups the sales orderlines by "Document_No".

Many thanks!

[srinivt · Accepted solution]

CALCULATE(CALCULATE(DISTINCTCOUNT(Sales[Document_No]), FILTER(ALL(Sales[Document_No]), CALCULATE(COUNT(Sales[Line_No])) = 5), Sales[Line_Amount] = 0), Sales[Manufacturer] = "Super Duper")

OR

CALCULATE(COUNTROWS(FILTER(VALUES(Sales[Document_No]), CALCULATE(COUNT(Sales[Line_No])) = 5)), Sales[Line_Amount] = 0), Sales[Manufacturer] = "Super Duper")

[legrand]

Hi srinivt,
thanks you for your reply. Without having the chance to test your solution at the moment it looks like all 5 rows from „super duper“ have to be line_amount = 0 to be counted by your formulas, am I right? The formula also has to work if just one of those 5 rows has the line_amount = 0.

The use case is an analysis of a promotion. The customer gets 5 items of the same manufacturer of which 4 are being invoiced and the 5th is free. I want to see, how often this constellation has been used. Maybe this helps explaining what I’m trying to achieve. Thanks so far!

[srinivt]

Yes the second option would have the issue not the first. The second option then would have to be tweaked slightly to account for that something like: 

CALCULATE(COUNTROWS(FILTER(VALUES(Sales[Document_No]), CALCULATE(COUNT(Sales[Line_No]), ALLEXCEPT(Sales, Sales[Document_No])) = 5)), Sales[Line_Amount] = 0), Sales[Manufacturer] = "Super Duper")

[legrand]

The measure works as expected but I didn't realize there could be more than one combination in one Document_No.

Example:

Article A, B, C, D, E all belong to Brand "Super Duper". In the same Document_No those Articles A, B, C, D have Line_Amount > 0, E has Line_Amount = 0. All articles have Quantity = 10. The formula shows Result = 1 but should show 10.

I think the easiest way to fix this would be to sum the Quantity of the article with Line_Amount=0 in Document_No which have 5 articles of "Super Duper" and one of these articles has Line_Amount=0.

If there are other articles than of Brand "Super Duper" with Line_Amount = 0 they must not be counted.

Is there a solution for this?

[legrand]

Hi srinivt,

I tried your first solution, works like a charm! Thanks alot!!