Forum Discussion
Sum Distinct Count
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
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
2 Replies
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 YuHi 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!