Create Measure for sum with group by

[Anonymous]

Hello,

I’m trying to learn DAX expression using this employee model

I would like to know if there is any way to create the below report (as a single report), where the same salary column from employees is aggregated individually based on the relationships between the tables,

or Is this even possible?

| Employee Id |	| Emp Salary |	| Region ID |	| Region Level Salaries |	| Job ID |	| Job Level Salaries |
| 100 |	| 24000 |	| 2 |	| 248200 |	| 04 |	| 24000 |
| 101 |	| 17000 |	| 2 |	| 248200 |	| 05 |	| 34000 |
| 102 |	| 17000 |	| 2 |	| 248200 |	| 05 |	| 34000 |
| 145 |	| 14000 |	| 1 |	| 74200 |	| 15 |	| 27500 |
| 146 |	| 13500 |	| 1 |	| 74200 |	| 15 |	| 27500 |
| 201 |	| 13000 |	| 2 |	| 248200 |	| 10 |	| 13000 |
| 108 |	| 12000 |	| 2 |	| 248200 |	| 07 |	| 12000 |

Measures (Region Level Salaries, Job Level Salaries)

[Icey]

Hi @Anonymous ,

I think what @amitchandak  provided has solved your problem, right?

Best Regards,

Icey

[Anonymous]

Hi @Icey 

Using the above DAX expression I created a measure (Salary by region) which partially solved the problem 

Salary by region:  calculate(sum(employees[salary]), allexcept(employees,regions, employees[salary]))

I understood ALLEXCEPT() -> removes all the filters applied on the table except for mentioned parameters

When I use this measure in the report this is the result

| employee_id |	| salary |	| region_id |	| Salary by region |
| 100 |		| 1 |	| 74200 |
| 101 |		| 1 |	| 74200 |
| 102 |		| 1 |	| 74200 |
| 145 |	| 14000 |	| 1 |	| 74200 |
| 146 |	| 13500 |	| 1 |	| 74200 |
| 176 |	| 8600 |	| 1 |	| 74200 |
| 100 |	| 24000 |	| 2 |	| 248200 |
| 101 |	| 17000 |	| 2 |	| 248200 |
| 102 |	| 17000 |	| 2 |	| 248200 |
| 145 |		| 2 |	| 248200 |
| 146 |		| 2 |	| 248200 |
| 176 |		| 2 |	| 248200 |

But I need the below output with filters applied on all other columns too, you can see the model relationships in my first question

| employee_id |	| salary |	| region_id |	| Salary by region |
| 145 |	| 14000 |	| 1 |	| 74200 |
| 146 |	| 13500 |	| 1 |	| 74200 |
| 176 |	| 8600 |	| 1 |	| 74200 |
| 100 |	| 24000 |	| 2 |	| 248200 |
| 101 |	| 17000 |	| 2 |	| 248200 |
| 102 |	| 17000 |	| 2 |	| 248200 |

Is this even possible...

Thanks

Seethend

[Icey · Accepted solution]

Hi @Anonymous ,

Try to use this measure:

Measure = IF(ISBLANK(SUM(employees[Salary])),BLANK(),[Your Sales by Region Measure])

Best Regards,

Icey

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

[Anonymous]

Thank you @Icey & @amitchandak 

These measures worked as expected

Salary by Region = calculate(sum(Employees[salary]), allexcept(employees, regions[region_id]))

Measure = IF(ISBLANK(SUM(employees[Salary])),BLANK(),[Salary by Region])

Thanks

Seethend

[amitchandak]

@Anonymous ,

Did not get it try like

calculate(sum(Table[salary]), allexcept(Table[Region_id]))

refer

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/