topic Re: Not able to get the result using DISTINCT to calculate the values in DAX Commands and Tips

Not able to get the result using DISTINCT to calculate the values

Anonymous — Fri, 10 Jan 2025 09:30:48 GMT

Hi Experts,

I am working on the scenario to calculate the total processes across all the unique accounts. but tried multiple ways of formula but nothing given an expected result.

below the table i am using (sample data) , processes type has many names and i am filtering only "No of processess in scope" values.
As per the below table , the total processes is 935 , but we have to sum each unique account values , in that case it should be 123

No.of Processes	Processes Type	Account Name
2	No of Processes in Scope	AAAA
48	No of Processes in Scope	BBB
48	No of Processes in Scope	BBB
48	No of Processes in Scope	BBB
48	No of Processes in Scope	BBB
48	No of Processes in Scope	BBB
48	No of Processes in Scope	BBB
2	No of Processes in Scope	CCCC
2	No of Processes in Scope	CCCC
2	No of Processes in Scope	CCCC
2	No of Processes in Scope	CCCC
2	No of Processes in Scope	CCCC
1	No of Processes in Scope	DD
1	No of Processes in Scope	DD
1	No of Processes in Scope	DD
1	No of Processes in Scope	DD
1	No of Processes in Scope	DD
70	No of Processes in Scope	SSS
70	No of Processes in Scope	SSS
70	No of Processes in Scope	SSS
70	No of Processes in Scope	SSS
70	No of Processes in Scope	SSS
70	No of Processes in Scope	SSS
70	No of Processes in Scope	SSS
70	No of Processes in Scope	SSS
70	No of Processes in Scope	SSS

I have used below DAX but it is still giving overall values only , please help to fix ..

SUMX(
DISTINCT('Test Delivery Updates (3)'[Account Name]),
CALCULATE(
SUM('Test Delivery Updates (3)'[No.of Processes]),
'Test Delivery Updates (3)'[Processes Type] = "No of Processes in Scope"
)
)

CALCULATE(SUMX(VALUES('Test Delivery Updates (3)'[No.of Processes]),CALCULATE(DISTINCT('Test Delivery Updates (3)'[No.of Processes]))),'Test Delivery Updates (3)'[Processes Type] = "No of Processes in Scope")

Thanks

Re: Not able to get the result using DISTINCT to calculate the values

ValtteriN — Fri, 10 Jan 2025 09:58:32 GMT

Hello,

Try this:

Measure 31 = SUMX(SUMMARIZE('Table (47)','Table (47)'[No.of Processes],'Table (47)'[Account Name]),[No.of Processes])

Summarize groups values to uniques and then SUMX for sum.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Not able to get the result using DISTINCT to calculate the values

Poojara_D12 — Fri, 10 Jan 2025 10:06:48 GMT

Hi Anonymous

To calculate the sum of processes for unique accounts, the formula uses:

SUMMARIZE: Groups data by Account Name and calculates the sum of No.of Processes for each account where Processes Type = "No of Processes in Scope."

SUMX: Sums up the results from the grouped data.

Total Processes for Unique Accounts = SUMX( SUMMARIZE( 'Test Delivery Updates (3)', 'Test Delivery Updates (3)'[Account Name], "Unique Processes", SUMX( FILTER( 'Test Delivery Updates (3)', 'Test Delivery Updates (3)'[Processes Type] = "No of Processes in Scope" ), 'Test Delivery Updates (3)'[No.of Processes] ) ), [Unique Processes] )

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Not able to get the result using DISTINCT to calculate the values

sanalytics — Fri, 10 Jan 2025 10:53:56 GMT

Anonymous

You can refer below measure as well

VAR _tbl = SUMMARIZE( FILTER( 'Table', 'Table'[Processes Type] = "No of Processes in Scope" ), 'Table'[Account Name] ) VAR _tblCol = ADDCOLUMNS( _tbl,"@MaxProcess",CALCULATE( MAX( 'Table'[No.of Processes] ) ) ) VAR _Result = SUMX( _tblCol,[@MaxProcess] ) RETURN _Result

Below screenshot

Regards

sanalytics

Re: Not able to get the result using DISTINCT to calculate the values

Anonymous — Fri, 10 Jan 2025 12:04:36 GMT

Poojara_D12 This is still giving me total values (including duplicate account rows as well) 😞

Re: Not able to get the result using DISTINCT to calculate the values

Anonymous — Fri, 10 Jan 2025 12:06:24 GMT

ValtteriN Here we need to filter only

[Processes Type] = "No of Processes in Scope"

as per the sample data I have only posted "No of processes in scope" type but we should add it in the DAX as well ..

Re: Not able to get the result using DISTINCT to calculate the values

Anonymous — Mon, 13 Jan 2025 08:15:10 GMT

Hi Anonymous ,

ValtteriN‘s workaround is good.

You can just modify his measure as

Measure = var _table=DISTINCT(SUMMARIZE(FILTER('Table',[Processes Type]="No of Processes in Scope"),'Table'[Account Name],'Table'[No.of Processes])) return SUMX(_table,[No.of Processes])

Sample data:

Result:

You can download my attachment for more details.

Best Regards,
Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Not able to get the result using DISTINCT to calculate the values

Anonymous — Fri, 17 Jan 2025 07:07:09 GMT

Anonymous
Thanks for the solution and sample 🙂