cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

DISTINCTCOUNT with FILTERED MEASURE

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

1 ACCEPTED SOLUTION
Community Champion

@Anonymous

try this alternative

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

Regards
Zubair

2 REPLIES 2
Community Champion

@Anonymous

try this alternative

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

Regards
Zubair

Anonymous
Not applicable

It does work! Thanks 🙂

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors