cancel
Showing results for
Did you mean:
Occasional Visitor

## DISTINCTCOUNT of Transactions given FILTERS

I have a column of sales transaction where each row is unique on a customer-invoice-sku basis. It looks like this:

InvoiceDate  InvoicePeriod  InvoiceNo  CustomerID  SKU  Qty  Total

------------------------------------------------------------------

05/01/2018   05/31/2018     I001       C001        RI   10   \$100

05/01/2018   05/31/2018     I001       C001        SW   20   \$150

05/01/2018   05/31/2018     I003       C002        RI   20   \$200

05/01/2018   05/31/2018     I004       C003        SW   30   \$300

04/14/2018   04/30/2018     I003       C001        RI   10   \$100

04/14/2018   05/31/2018     I004       C002        SW   20   \$300

I'm trying to create a visualization that counts number of unique customers by InvoicePeriod, filtered by SKU (or not in the case of totaling). The resulting visualization table should look something like this:

Count of customers by InvoicePeriod

SKU    04/30/2018  05/31/2018

RI     1           2

SW     1           2

All    2           3

The idea is that this table should update based on Slicer selections. I know this requires a measure, but everything I write ignores filters and just repeats the same value in every cell.

Apologize for being such a newbie. Any pointers would be sincerely appreciated. Thanks much.

2 REPLIES 2
Established Member

## Re: DISTINCTCOUNT of Transactions given FILTERS

Not sure if I understand your requirement correcntly.

You can use the following DAX to get the unique count of customers filtered by SKU

`Count_Customer = COUNTROWS(GROUPBY(invoice,invoice[CustomerID],invoice[SKU]))`

And then create a Matrix to get the following table:

Member

## Re: DISTINCTCOUNT of Transactions given FILTERS

I presume you tried this measure?

Customers = DISTINCTCOUNT(Table1[CustomerID])

I can't see why this wouldn't work and give you a correct result no matter which way you slice it.

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte