Forum Discussion
SUMX with Distinct count on different column
Hi Leigh_123 ,
Please have a try.
Business area = VAR __SearchTerms = ADDCOLUMNS ( 'Business area filter', "Count", COUNTROWS ( FILTER ( 'Query1', FIND ( 'Business area filter'[Business Area reference],'Query1'[Your column name],, 0 ) > 0 ) ) ) RETURN SUMX ( DISTINCT(__SearchTerms, 'Query1'[Your column name]), [Count] )How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super UserPlease provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
| ID | Business Area | Country |
| 1 | Tax; Consulting | Korea |
| 2 | Tax | France |
| 3 | Audit | UK |
| 4 | Audit | Ireland |
| 5 | Audit; Tax; Consulting | Australia |
| 6 | Tax | China |
| 7 | Audit; Tax | Germany |
| 8 | Consulting | Zimbabwe |
| 9 | Consulting; Tax | South Africa |
| 10 | Consulting; Tax | US |
| 11 | Audit | Switzerland |
| 12 | Audit | Switzerland |
| 13 | Financial Advisory | India |
| 14 | Risk Advisory | Japan |
| 15 | Audit; Tax | Germany |
| 16 | Risk Advisory | Spain |
| 17 | Financial Advisory; Audit | Norway |
| 18 | Audit; Tax | Spain |
| 19 | Enabling Area | Egypt |
| 20 | Enabling Area | Egypt |
| 21 | Audit; Tax | Belgium |
| 22 | Tax | Greece |
| 23 | Tax | Italy |
| 24 | Conulting; Tax; Audit | Turkey |
| 25 | Audit; Enabling Area | Portugal |
| 26 | Audit | Latvia |
| 27 | Audit; Tax | Lithuania |
| 28 | Audit; Tax; Consulting; Risk Advisory | Georgia |
29 | Financial Advisory; Audit; Tax | Barbados |
Providing the attached sample data. Escentially i need a distint count of "Business Area" based on "Country". I can do this with individual dax for each bsiness area:
count (A&A) =
Calculate(
DISTINCTCOUNT('Dummy data'[Country]),
CONTAINSSTRING('Dummy data'[Business Area],"Audit"))
However if i want to add this to a bar chart i get a mess:
I am looking to achieve the above in the format below:
I achieved this with the folowing Dax:
Business area percentage =
VAR __SearchTerms =
ADDCOLUMNS (
'Business area filter',
"Count",
COUNTROWS (
FILTER (
'Dummy data',
FIND ( 'Business Area Filter'[Business Area reference],'Dummy data'[Business Area],, 0 ) > 0
)
)
)
RETURN
SUMX ( __SearchTerms, [Count] )
Is there any way to edit this to count distinct? Apologies, i am unable to provide pbix.
Thank you in advance
- lbendlin
I would recommend you solve that issue in Power Query to bring your source data into a usable format. Then the visuals will work automatically.
Note you have a data quality issue with "Conulting".
See attached