Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Distinct Count Total is not correct

  • 1.  Distinct Count Total is not correct

    Bronze Contributor
    Posted 2 days ago
    I have a Matrix showing Product Categories, Total Customers (Distinct Count), and Total Sales. When I manually total the distinct count of customers, I get 3,082, which doesn't match the total at the bottom of 1,971.​

    How can I get the total to correctly sum the categories?

    ------------------------------
    Mary Moore
    Sales Operations Analyst
    6153248429
    ------------------------------


  • 2.  RE: Distinct Count Total is not correct

    Posted 2 days ago

    Hi Mary,

    Your total is probably correct :)  But your total is not the sum of the distinct customers.  It is still a distinct count of all the customers that bought something.

    Example: You have 546 different customers that bought Accessories.  Suppose that all those 546 customers also bought a product from the Laryngeal Mask Airway.

    Then the total different customers for Accessories and Laryngeal Mask Airway would still be 800 because you may not count the same customer twice (distinct count)

    I hope this helps.

    Regards,

    Johan



    ------------------------------
    Johan Vermeire
    IT Trainer
    Xylos nv
    0000000
    ------------------------------



  • 3.  RE: Distinct Count Total is not correct

    Bronze Contributor
    Posted 2 days ago
    Hi Johan,

    Thanks for your reply. I agree with your explanation, but I'm trying to get the sum so that I can take that total of 3,082 and divide it by 1,971 to get the average number of products bought by customer. I'm thinking this may not be possible in BI.

    Mary

    ------------------------------
    Mary Moore
    Sales Operations Analyst
    Franklin TN
    6153248429
    ------------------------------



  • 4.  RE: Distinct Count Total is not correct

    Posted yesterday

    Mandy,

    2 remarks:

    1) If you want to do it the way that you've mentioned in your reply, don't you have than the same problem on the subtotals of the categories?

    For example:  the category Accessories has a total of 546 unique customers, but it can be that the sublevel that is showing 3 products (I guess it are products) with values like: 200, 300 and 400 different customers, but some customers appear for different products within that category.   Don't you want to see then 900 as the total? Or is it only in the grand total of the categories that you want that other approach?

    2) I would solve it like this (without taken into account my first remark)

    measure = SUMX(   VALUES(DimCategories[Category]) , [Total customers])

    But I don't know whether it's a better formula than the one from James. 

    Good luck



    ------------------------------
    Johan Vermeire
    IT Trainer
    Xylos nv
    0000000
    ------------------------------



  • 5.  RE: Distinct Count Total is not correct

    Posted 2 days ago
    Edited by James Yates 2 days ago

    I had a similar issue recently. I solved by using countrows and groupby. 

    Try something like

    measure = COUNTROWS( GROUPBY(sales, sales[category], sales[customer]) )​


    ------------------------------
    James Yates
    Management Analyst 3
    ------------------------------