Using DAX: Removing Duplicates in Column A Before Summing Up Column B

[markefrody]

Hi,

I would like to ask you assistance if there is a DAX query that could help me on the below. Please note that I cannot use remove duplicates in "Edit Queries" since I have other calculations that I am using for that table.

I'm trying the following steps in this order:

1.) Temporary remove duplicate records in column "Job: Name" in order to do step #2 

2.) Add up all the numbers found in column "SPHA's Filled" based on "Job: Name".

3.) Group those numbers in step #2 based on "Office".

Note: I want to retain all the fields in the table not just "Job: Name", "SPHA's Filled", and "Office".

Please see the screenshot of the query I am working with:

File

Appreciate your kind assistance and please do let me know if you have further questions.

Best regards,
Mark V.

[Anonymous]

Hi @markefrody,

You can create a new DAX table with this formula:

NewDAXTable =
SUMMARIZECOLUMNS (
    'Table'[Job: Name],
    "Sum_SPHA_Filled", MAX ( 'Table'[SPHA's Filled] )
)

Regards.

[markefrody]

Hi @Anonymous,

Thanks for the suggestion. Is it posibble to group it also via "Office"?

[MFelix]

Hi @markerfrody

Add a new table with the following syntax

Nem table =
SUMMARIZE (
ALL ( Table[Job: Name]; Table[SPHA'S Filled] );
"Job"; DISTINCT ( Table[Job: Name] );
"SPAS"; SUM ( Table[ SPHA'S Filled] )
)

Regards,
MFelix

[markefrody]

Hi @MFelix, I used the DAX you provided but it is giving me this error:

"A table of multiple values was supplied where a single value was expected."

Here is the DAX query used:

New Table =
SUMMARIZE(

ALL('SPHA Fulfillment'[Job: Name], 'SPHA Fulfillment'[SPHA's Filled]),
"Job", DISTINCT('SPHA Fulfillment'[Job: Name]),
"SPAS", SUM('SPHA Fulfillment'[SPHA's Filled])

)

[MFelix]

Hi @markefrody,

Made an error on my formula if you use this one this will make distinct values of job name and Office:

Table = 
SUMMARIZE (
    ALL ( Table1[Job:Name]; Table1[Office]; Table1[SPHA's FIlled] );
    Table1[Job:Name];
    Table1[Office];
    "SPHA"; SUM ( Table1[SPHA's FIlled] )
)

In the summarize when you choose a column it will automatically give you the distinct value in a column so no need to do it again that's why it was giving you the error. See the result below:

Regards,

MFelix

[markefrody]

Thanks for your assistance @MFelix. It seems that what I'm looking for is a bit more complex than I expected. I posted a new post explaining the output I expect.

[Anonymous]

By what criteria do you want to remove duplicates? For example, the two "debottleneck" entries in your screenshot both have one "SPHA Filled". Let's say they didn't both have one, but rather the first had 10, and the second had 5. Which one would you want to keep? Or do you not care/is it arbitrary? Or is "SPHA Filled" always one or zero?

[markefrody]

Hi @Anonymous,

No criteria in removing duplicates. I just want it to be removed for the sake of counting all the numbers in "SPHA filled". If the removed entry has a number in "SPHA filled" it should not be counted.