Sum Distinct Count

[Anonymous]

Hi Folks,

I created dax to distinct count each material that appear for each market.

Table Name: OrderIntake

Distinct_Count_Material = DISTINCTCOUNT(OrderIntake[Material])

Material	Indonesia	Malaysia	Mongolia	Philippines	Vietnam
63250401001	1	1	1	1	1
63251401001	1	0	1	1	1
63251401014	1	1	1	1	0
63251401178	0	1	1	1	1
63251401375	0	1	1	1	0

Question, how do I sum the distinct for each material?

Desired output =

material# 63250401001  = 5

And would it be possible to then show it as a percentage over 5 countries?

e.g. 63250401001 = 5/5 = 100%,

63251401001 = 4/5 80%

Thanks for the help in advance.

Regards

Hidayat

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

1. If your sample data is raw data in Data view, you can follow below steps:

(1). Create calculated columns below:
sum = VALUE([Indonesia])+VALUE([Malaysia])+VALUE([Mongolia])+VALUE([Philippines])+VALUE([Vietnam])
Percentage = DIVIDE(OrderIntake[sum],COUNTROWS(OrderIntake))

(2).Modify the column[percentage], format it from general to percentage.

(3).Create a Table visual in your report and add the [Material], [Indonesia], [Malaysia], [Mongolia], [Philippines], [Vietnam], [Sum] and [Percentage] fields.

You can also download the PBIX file "Sum Distinct Count1" to have a view.

2. If your sample data is from a matrix visual, you can refer to below steps:
(1).You can create two measures below:
Distinct_Count_Material =
IF(COUNT(Sheet2[Material])=CALCULATE(COUNT(Sheet2[Material]),ALLEXCEPT(Sheet2,'Sheet2'[Material])),CALCULATE(COUNT(Sheet2[Material]),ALLEXCEPT(Sheet2,'Sheet2'[Material])),COUNT(Sheet2[Country]))

Percentage = IF(COUNT(Sheet2[Material])=CALCULATE(COUNT(Sheet2[Material]),ALLEXCEPT(Sheet2,'Sheet2'[Material])),CALCULATE(COUNT(Sheet2[Material]),ALLEXCEPT(Sheet2,'Sheet2'[Material])),BLANK())/CALCULATE(DISTINCTCOUNT(Sheet2[Country]),ALL(Sheet2))

(2).You can create a Matrix visual and add your [Material], [Country], [Distinct_Count_Material] and [Percentage] fields.

You can also download the PBIX file "Sum Distinct Count2" to have a view.

Best Regards,
Qiuyun Yu

[Anonymous]

Hi Qiuyun, 

Thanks for the feedback. The solution is as Distinct Count 2 file.

Using Distinct Count 2.pbix, If I were to add column "group" to sheet 2 table and group countries together , how do I achieved below table output?

Group Column Defination

Indonesia & Malaysia = Group A

Mongolia & Philippines = Group B

Vietnam = Group C

Desired Output

Material	Group A	Group B	Group C	Total
63250401001	1	1	1	3
63251401001	1	1	1	3
63251401014	1	1	1	3
63251401178	1	1	0	2
63251401375	1	1	0	2

Many thanks!