Forum Discussion
SUMX with Distinct count on different column
Hi all,
I have calculation that i have created from online research that counts string column ('Query1'[Business(es) Area]) and filters by a predefined table 'Business area filter':
I want to be able to have a distinct count of the SUMX but based on a different column in 'Query1', is this something that is possible?
current dax below
Business area =
VAR __SearchTerms =
ADDCOLUMNS (
'Business area filter',
"Count",
COUNTROWS (
FILTER (
'Query1',
FIND ( 'Business area filter'[Business Area reference],'Query1'[Business(es) Area],, 0 ) > 0
)
)
)
RETURN
SUMX ( __SearchTerms, [Count] )
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.
4 Replies
- lbendlin
Super User
Please 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/1447523ID 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
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.
