cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

1 ACCEPTED SOLUTION
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:

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

8 REPLIES 8
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:

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

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/

Anonymous
Not applicable

@vivran22 The result is in the bottom session

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

Tier 1

Region 1

Community Champion

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

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.

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.

Community Champion

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

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

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

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors