Forum Discussion
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
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
- Pragati11
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
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% )
- vivran22
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 LinkedInvivran22 The result is in the bottom session
# OF Order | Total # of Cust | % Cust ordered
Tier 1
Region 1
- vivran22
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
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
