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

[dinesharivalaga]

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

DK

[sanalytics]

@dinesharivalaga 

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

[Poojara_D12]

Hi @dinesharivalaga 

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 

[dinesharivalaga]

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

[ValtteriN]

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/

[dinesharivalaga]

@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 .. 

[Anonymous · Accepted solution]

Hi @dinesharivalaga ,

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

[dinesharivalaga]

@Anonymous 
Thanks for the solution and sample 🙂