Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

distinct count and group by

I want to find out what % of customer order by Region and Tier.    I can get the count of how many order by dropping to value and changed to distinct count,  But i cant get the total count of each...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 


    The measure should something like the follow: 

     

    Count OF Cust Ordered = CALCULATE(DISTINCTCOUNT('Table'[Cust]),ALLEXCEPT('Table','Table'[Tier],'Table'[Region]),FILTER('Table','Table'[Order]<>BLANK()))

% = [Count OF Cust Ordered]/COUNT('Table'[Cust])

     

    Matrix:

     

    Community Support Team _ Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

vivran22's avatar
vivran22
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous 

 

There are multiple ways of achieving this. One below is without using any measures

 

Here is the sample dataset I have created for this purpose:

 

Then I have used the matrix visual, and dragged Order Quantity thrice under Values:

 

  1. I selected Count from the field properties
  2.  I selected Sum from field properties
  3.  I selected Sum from field properties > Show Value as > Percent of column total

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

vivran22 thank your very much for your help. but NO. thats not what i am looking for. 

 

I am looking for % of #Cust place an order. (Yes - ordered; NO - didnt ordered) . Like 10 out of 20 ordered. 50% placed order. 

 

In your example. every cust had ordered. thats 100% placed order...

but not every Cust is in the fact or transcation table; so thats why i cut out 4 cust in my example. total 20 cust only 16 had ordered. 

  • vivran22's avatar
    vivran22
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

     

    You requirement was not as detailed in your orignal post. Basis your recent input, may I suggest you to have a separate table for maintaing list of customers:

     

    Customer table:

     

     

    and separate table for sales order:

     

     

    Create the relationship between these two tables:

     

     

    and then use following measures to get the desired results:

     

    Total Customers = COUNTROWS(dtCust)

Customer Ordered = DISTINCTCOUNT(dtSales[Customer])

% Ordered = DIVIDE([Customer Ordered],[Total Customers])

     

    Result:

     

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution
    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    Connect on LinkedIn