Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
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
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

try this alternative

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

try this alternative

 

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

It does work! Thanks 🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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