Dax for sum of distinct values grouped by multiple columns

[Anonymous]

Hi community,

Following is my sample dataset, I want to add a column "Total Sal"(already added in the following snapshot) that is total of distinct salaries of employees. Grouped by Dept, position,empcode.

I have tried few DAX, but nothing is working.

Your help is much appretiated.

Many thanks,

Meena

[sivapandi]

Table =
SUMMARIZE (
    EmpSalary,
    EmpSalary[Dept],
    EmpSalary[empcode],
    EmpSalary[Position],
    "TotalSal", DISTINCT ( EmpSalary[salary] )
)

Hai Meena.,

Please try this....

[Anonymous · Accepted solution]

Another community member could probably improve on this, but I did this by changing the grain of the table, i.e. removing the qualification column and consolidating the duplicates. You can do this in a separate table by referencing the query in Query Editor.

Then you can add in the measure:

Total Departmental Salary =
CALCULATE ( SUM ( Salaries[Salary] ), ALLEXCEPT ( Salaries, Salaries[Dept] ) )