DAX code to count orders with crossed categories

0
votes

I've got a list of units which are being sold by a shop. Each line describes date, order number, number of units and category (A and B in this example).

How can I, using just DAX measures in a pivot table, know how many orders are both in categories A and B per day? I've tried using CALCULATE in several ways, but I can't find the correct way to do it...

In this link I show you a screenshot of a sample table1

The pivot table should show:

25/05/2016  3 (orders 100, 104 and 105 have units from categories A and B)  
26/05/2016  1 (order 200 has units from categories A and B)

Thanks

2
excelpowerpivotdax

2 Answers

1
votes

Create a measure that counts the distinct OrderNo. The just create your pivot table and use that measure. You can use two expressions for the measure.

Excel 2010:

=COUNTROWS(DISTINCT(TableName[OrderNo]))

Excel 2013+

=DISTINCTCOUNT(TableName[OrderNo])

With the measure created in your model create a pivot table. Drag and drop Date field to rows, Category field to columns and the created measure to Values.

You will get something like this:

enter image description here

Ignore the labels excel put to the pivot table, my OS language setting is Spanish.

UPDATE: Based on your comment, you need the distinct OrderNo's that have Categories A and B.

Use the following expression:

=CALCULATE (
    COUNTA (TablaName[OrderNo]),
    CALCULATETABLE ( VALUES ( TablaName[OrderNo] ), TablaName[Category] = "A" ),
   TablaName[Category] = "B"
)

I was thinking of using intersect function which is not supported in Excel 2010 but this is a general way to get your expected result.

This is the pivot table generated from it.

enter image description here

Hopefully this is what you are looking for, let me know if it helps.

0
votes

Create a calculated column with the following formula:

= if( CALCULATE(DISTINCTCOUNT(mytable[Category]),filter(all(mytable),mytable[Date]=EARLIER(mytable[Date])&&mytable[OrderNo]=EARLIER(mytable[OrderNo])),or(mytable[Category]="A",mytable[Category]="B")) =2,true(),false()) The earlier function compares rows with same date and orderNo. The extra or filter, only takes into account the category A and B.

This will return true/false on orders with category A and B. Since you can't do a regular count, because you have multiple lines for a ordernr. You need a distinctcount.

new measure:

=CALCULATE(DISTINCTCOUNT(mytable[OrderNo]),mytable[CalcColumn01]=true())