topic Re: distinct count and group by in Desktop

distinct count and group by

Anonymous — Wed, 26 Feb 2020 14:26:00 GMT

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 group. Can anyone help with the measure please ! Thank you

Re: distinct count and group by

Pragati11 — Wed, 26 Feb 2020 14:49:30 GMT

Hi Anonymous ,

Just wanted to check if you are trying to do this in excel?

In excel you can create a Pivot table for "Product" column.

If this helps please give Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

Re: distinct count and group by

vivran22 — Wed, 26 Feb 2020 14:55:03 GMT

Hello Anonymous,

What do you mean by count of each group? Can you share the expected result based on the sample data shared?

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

Re: distinct count and group by

Anonymous — Wed, 26 Feb 2020 15:14:48 GMT

vivran22 The result is in the bottom session

# OF Order | Total # of Cust | % Cust ordered

Tier 1

Region 1

Re: distinct count and group by

Anonymous — Wed, 26 Feb 2020 15:17:33 GMT

Pragati11 No. i am trying to do it in Power BI. just use EXCEL to explain my need. lol...

And no, i need to know what % of Cust order. ( like x% )

Re: distinct count and group by

vivran22 — Wed, 26 Feb 2020 15:26:58 GMT

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:

I selected Count from the field properties
I selected Sum from field properties
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

Re: distinct count and group by

Anonymous — Wed, 26 Feb 2020 15:38:51 GMT

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.

Re: distinct count and group by

Anonymous — Thu, 27 Feb 2020 03:33:41 GMT

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

Re: distinct count and group by

vivran22 — Thu, 27 Feb 2020 06:49:05 GMT

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