Sum With Multiple Filters

[rderrickwhite]

here's an easier one...  or at least it should be, I've looked in previous posts but didn't see this situation addressed...  I am attempting to write a formula to sum column c in the below example, under the following conditions:
1. column a contains the same value 
2. column b is a distinct value 

3. I need to sum column c under the condition that the data from column a is similar (same part number), but only if the location is unique.

4. for the purposes of my data model I need this to occur as a column rather than a measure.

 
It seems like it should be simple to resolve, but so far all of my idea have returned the incorrect value.

[v-frfei-msft]

Hi @rderrickwhite ,

Please check the following steps as below.

1. Insert an index column in power query.

2. Create the calculated columns as below.

Column = var ind = 'Table'[Index] -1
return
IF(CALCULATE(MAX('Table'[PN]),FILTER('Table','Table'[Index] = ind)) <>'Table'[PN],1,0)

catgory = CALCULATE(SUM('Table'[Column]),FILTER('Table','Table'[Index] <= EARLIER('Table'[Index])))

Result = CALCULATE(SUM('Table'[QTY AVAILABLE]),ALLEXCEPT('Table','Table'[catgory]))

[rderrickwhite]

@Mariusz,  Thank you but this suggestion does not meet the criteria I specified.  It simply sums Qty available...  The challenge is that if the QTY Available from the same location appears on multiple rows,  the total will be duplicated.

[Mariusz]

Hi @rderrickwhite 

Sorry I was under ipression that this was the requierment.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz , I think that @rderrickwhite wants to ignore duplicates in his table.

You will probably have a better solution but one way to do it is to first remove duplicates from your table 

Table2 = DISTINCT('Table')

And then use @Mariusz formula

Column = 
CALCULATE(
    SUM( 'Table'[QTY AVAILEBLE] ),
    ALLEXCEPT( 'Table', 'Table'[PN] )
)

It's not very clean and i think you could have a much better result by using Power Query instead of dax, but at least this will work.

[rderrickwhite]

what would be the solution in Power Query?

[Anonymous]

Something like that would work 

let
    Source = yourTable,
    #"Remove Duplicates" = Table.Distinct(Source),
    #"GroupBy" = Table.Group(#"Remove Duplicates", {"PN"}, {{"SUM", each List.Sum([QTY AVAILABLE]), type number}}),
    #"JoinTables" = Table.NestedJoin(#"Type modifié", "PN", #"GroupBy","PN","total"),
    #"Develop" = Table.ExpandTableColumn(#"JoinTables", "total", {"SUM"}, {"total.SUM"})
in
    #"Develop"

Tell me if you encounter any problems

It's better with power query because with DAX you cant use a calculated table in a SUM, you have to use an existing table.

[rderrickwhite]

Thanks!  That worked, but it isn't the most elegant solution.  I wish there was a way that did not include making another table...  It's just making my data set take longer to load.

[Anonymous · Accepted solution]

Hi @rderrickwhite ,

I do agree with you that it isn't elegant. Here is a solution that works :

TOTAL = CALCULATE(SUMX('Table';'Table'[QTY AVAILABLE]/
COUNTX ( FILTER ( 'Table'; EARLIER ( 'Table'[LOCATION] ) = 'Table'[LOCATION] ); 'Table'[LOCATION] ) );ALLEXCEPT('Table';'Table'[PN]))

If you want to understand how it works, I described it below

1. Find the number of occurences of each LOCATION 

OCCURENCES = COUNTX ( FILTER ( 'Table'; EARLIER ( 'Table'[LOCATION] ) = 'Table'[LOCATION] ); 'Table'[LOCATION] )

2. Divide QTY AVAILABLE by the number of occurences found 

CalculatedQTY = 'Table'[QTY AVAILABLE]/'Table'[OCCURENCES]

3. Sum this calculated occurences

RESULT = CALCULATE( SUM( 'Table'[CalculatedQTY] ); ALLEXCEPT( 'Table'; 'Table'[PN] ))

Tell me if it is any better for you,

Regards, 

Etienne

[Swamy3105]

@Anonymous 

How to get the unique values from another table and comparing multiple column in both table.

Please advise.

Regards.,

Swamy

[rderrickwhite]

@AnonymousThis is indeed a very elegant solution to the problem, and seemingly, the most direct.  

@v-frfei-msftyour solution also worked well.

Thank you both for taking the time to help me out.

[rderrickwhite]

It is returning the correct result, but only because in the example you don't have multiple rows with the same part number and location.

[Mariusz]

Hi @rderrickwhite 

Please see the below

Column = 
CALCULATE(
    SUM( 'Table'[QTY AVAILEBLE] ),
    ALLEXCEPT( 'Table', 'Table'[PN] )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.