Excel SUMPRODUCT formula in DAX or PowerQuery (M)

[Anonymous]

Hello, 

I have a report in excel that has a column with a sumproduct formula to calculate the count the number of times remaining a person's name shows up in a specific department.  

Example data is below with a desired output column.

Department Full Path	Job Name (1)	Default Location	Pay Type	Full Name	Desired Output
Tooling - Fixtures	2020 Direct Labor Jobs	Production	Non-Exempt	Person 4	1
Tooling - Fixtures	2020 Direct Labor Jobs	Production	Non-Exempt	Person 5	1
Tooling - Fixtures	2020 Direct Labor Jobs	Production	Non-Exempt	Person 1	3
Tooling - Fixtures	2020 Direct Labor Jobs	Production	Non-Exempt	Person 3	1
Tooling - Fixtures	Indirect Labor	Production	Non-Exempt	Person 1	2
Tooling - Fixtures	Direct Labor Jobs	Production	Non-Exempt	Person 1	1
Tooling - Form	Indirect Labor	Production	Non-Exempt	Person 1	3
Tooling - Form	2020 Direct Labor Jobs	Production	Non-Exempt	Person 3	1
Tooling - Form	2020 Direct Labor Jobs	Production	Non-Exempt	Person 1	2
Tooling - Form	2020 Direct Labor Jobs	Production	Non-Exempt	Person 6	1

The SUMPRODUCT formula used is =SUMPRODUCT((E2:$E$10001=E2)*(A2:$A$10001=A2)).

Person 1 is the best example of what I would like to achieve, either as a custom column in PQ or as a measure in DAX.  You can see Person 1 starts off as a count of 3 as there are 3 person 1's in Tooling - Fixtures.  This number then decreases 2, 1 as the next records containing person 1 are found in the Tooling - Fixtures department. 

After reading forums on here I messed around with SUMX however, I could not achieve the desired output. 

I greatly appreciate any help.

Thanks,

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

Column =
CALCULATE (
    COUNT ( 'Table'[Full Name] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Department Full Path], 'Table'[Full Name] ),
        'Table'[Index] >= EARLIER ( 'Table'[Index] )
    )
)

BTW, .pbix file attached.

Best Regards,

Icey

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

[Greg_Deckler]

@Anonymous - See this article:

https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

Which will then take you to here:

https://community.powerbi.com/t5/Community-Blog/S-Excel-to-DAX-Translation/ba-p/1061121

Which provides this equivalent:

SUMPRODUCT = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table1',
            "Value",[Value1] * RELATED(Table2[Value2])
        )
RETURN
    SUMX(__Table,[Value])