topic Re: How to calculate distinct sum with filter in DAX Commands and Tips

How to calculate distinct sum with filter

jvrnicfi — Thu, 22 Jun 2023 04:34:52 GMT

Hi there, newbie questons

I have flat datatable (800 thousand rows)

I want the sum DISTINCT PLACE,WRKNR,WRKROW,MECHANIC WHERE SOURCE = WORKORDER

something like this:
SELECT DISTINCT PLACE,WRKNR,WRKROW,MECHANIC
CALCULATE SUM(TPOH * TPOPROS / 100)
WHERE SOURCE is WORKORDER

But how?

Re: How to calculate distinct sum with filter

some_bih — Wed, 21 Jun 2023 08:49:51 GMT

Hi jvrnicfi for sum two columns and product betwen them you just need measure like Sum Measure. In visual select wanted column. I hope this help
Sum Measure =

SUMX (

FILTER ( WORKORDER, WORKORDER[SOURCE] = "WORKORDER" ),

WORKORDER[TPOH] * WORKORDER[TPOPROS]

)

Re: How to calculate distinct sum with filter

devanshi — Wed, 21 Jun 2023 08:59:55 GMT

Total = [TPOH] * [TPOPROS]
CALCULATE(SUM([Total]), FILTER('TableName', DISTINCT([Source]) = "WORKOREDER"))

Re: How to calculate distinct sum with filter

tamerj1 — Wed, 21 Jun 2023 10:36:08 GMT

Hi jvrnicfi

Please try

TPO hours = SUMX ( FILTER ( 'Table', 'Table'[SOURCE] = "WORKORDER" ), 'Table'[TPOH] * 'Table'[TPOPROS] / 100 )

Re: How to calculate distinct sum with filter

jvrnicfi — Thu, 22 Jun 2023 04:42:04 GMT

Thank you for your answer, solution is correct. Result is same that my own answer example.

However i misleading my queston by my own answer. Thats why i change the picture to clarify my queston.

Main problem is how to calculate with distinct columns (there is many lines that must be ignored)

Re: How to calculate distinct sum with filter

jvrnicfi — Thu, 22 Jun 2023 04:44:38 GMT

Thank you for your answer, solution is correct. Result is same that my own answer example.

I do the same focus as I did for "tamerj1"

However i misleading my queston by my own answer. Thats why i change the picture to clarify my queston.

Main problem is how to calculate with distinct columns (there is many lines that must be ignored)

Re: How to calculate distinct sum with filter

jvrnicfi — Thu, 22 Jun 2023 04:44:58 GMT

Thank you for your answer, solution is correct. Result is same that my own answer example.

I do the same focus as I did for "tamerj1"

However i misleading my queston by my own answer. Thats why i change the picture to clarify my queston.

Main problem is how to calculate with distinct columns (there is many lines that must be ignored)

Re: How to calculate distinct sum with filter

tamerj1 — Thu, 22 Jun 2023 06:50:24 GMT

Hi jvrnicfi
Please try

TPO hours = SUMX ( SUMMARIZE ( FILTER ( 'Table', 'Table'[SOURCE] = "WORKORDER" ), 'Table'[WRKNR], 'Table'[WRKROW], "@TPOH", SUMX ( TOPN ( 1, 'Table', 'Table'[PARTSROW], ASC ), 'Table'[TPOH] ), "@TPOPROS", SUMX ( TOPN ( 1, 'Table', 'Table'[PARTSROW], ASC ), 'Table'[TPOPROS] ) ), [@TPOH] * [@TPOPROS] / 100 )

Re: How to calculate distinct sum with filter

devanshi — Thu, 22 Jun 2023 07:09:15 GMT

Try this once,
DistinctSum = CALCULATE( SUM('Table'[TPOH] * 'Table'[TPOPROS] / 100),
'Table'[SOURCE] = "WORKORDER",
ALLEXCEPT('Table', 'Table'[PLACE], 'Table'[WRKNR], 'Table'[WRKROW], 'Table'[MECHANIC]) )