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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

Capture.PNG

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@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:

Matrix.JPG

 

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

View solution in original post

8 REPLIES 8
V-pazhen-msft
Community Support
Community Support

@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:

Matrix.JPG

 

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

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

Anonymous
Not applicable

@vivran22 The result is in the bottom session 

      

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

Tier 1

       Region 1 

 

@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:

dataset.PNG

 

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

Visual.PNG

 

  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

% of grand total.png

 

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
Not applicable

@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. 

@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:

 

customer table.PNG

 

and separate table for sales order:

 

sales table.PNG

 

Create the relationship between these two tables:

 

relationship.png

 

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:

 

visual2.PNG

 

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

Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

@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% )  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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