DISTINCTCOUNT with FILTERED MEASURE

[Anonymous]

Hi there,

I am quite new to PowerBI and still struggling to understand the calculations performed behind the functions. Commited to learn thought! 🙂

My struggle right now is the following. I have four variables:

1. "CustomerID" - customer identification code
2. "TransactionDate" - normal purchase date
3. "Product Category" - Music, Movies and Books
4. "Product Sales" - $$ figure

I would like to know two things:

• What percentage of a customer purchases is dedicated to buy books? This one seems to work as expected.
  Book Purchases (%) =

  VAR __MEASURE_VALUE =

      CALCULATE(

          SUM('Sales'[ProductSales]) + 0,

          'Sales'[ProductCategory] = { "Books" }

      )

  VAR __BASELINE_VALUE = SUM('Sales'[ProductSales])

  RETURN

      DIVIDE(__MEASURE_VALUE, __BASELINE_VALUE)

• How many unique customers dedicate 50% of more of their purchases on buying Books?
  Unique Customers, +50% Books (#)=

  CALCULATE(

       DISTINCTCOUNT('Sales'[CustomerID]),

       FILTER('Sales','Sales'[BookPurchases (%)] >= 0.5)

       )

The second function doesn't want to work. I am not sure where the problem is but it ends-up counting any unique customer with books sales, independently of any percentage.. I wonder whether is has to do with using a measure a metric for the filter. Thoughts? 🙂

Thanks,

Eloy

[Zubair_Muhammad · Accepted solution]

@Anonymous

try this alternative

Unique Customers, +50% Books (#) =
COUNTROWS (
    FILTER ( VALUES ( 'Sales'[CustomerID] ), [BookPurchases (%)] >= 0.5 )
)

[Anonymous]

It does work! Thanks 🙂