DAX Measure SUM based on slicers

0
votes

I have the following data:

Table A

Emp_ID, Weekending, Date, Dept_ID, Status, Hrs

Table B

Dept_ID, Dept_Nm, Dept_HOD (relationship: Dept_ID)

I would like to create a matrix table with the following:

Columns: Weekending
Rows: Emp_ID
Value: Using DAX measure if(Status="F",SUM(Hrs)-44,SUM(Hrs)-22)

where the table can be controlled by the Slicers: Weekending, Dept_Nm, Dept_HOD

Any advice on how I can create the DAX measure?

enter image description here

The red box ID are not suppose to be there as they are not in the selected department

1
powerbidax
Since you need row based calculations, you need SUMX – W.B.
Could you please advise on how I can use SUMX? I tried using SUMX(Table A, Hrs) - 44. It works when I dont select any slicer, if I select the filter, those that are not supposed to be in the department will have -44 – IcySkullz
You would have to show your sample data and expected output. Otherwise it's just guessing, not diagnosing. – W.B.
@W.B. I have added a screenshot. The left table shows the sum of all the hrs based on the selected department. Where the right table supposed to be the sum of hrs - 44 and only those emp_id in the selected department – IcySkullz
I think you want to subtract 44 within the sumx, that's how I understood your requirement. SUMX(Table, Table[Hrs] - if(Table[Status]="F", 44, 22)). But that depends on the underlying data, which would also be useful to see. It might be that you want to sum all the rows per employee and then subtract 44 or 22, but that's not clear from the question. – W.B.

1 Answers

0
votes

I'm on the go, so I'll just type from the top of my head. I would use SUMMARIZE with SUMX. From what I can see, you want to summarise numbers weekly as well. This would be something along these lines:

SUMX(SUMMARIZE('Table A', [Emp_ID], [Status], [Weekending], "Hrs", SUM([Hrs])), [Hrs] - IF([Status] = "F", 44, 22)).

Don't have means to check it atm, if it doesn't work, let me know.

What it does is it creates a calculated table that summarises hours by employee, status and week ending. Then based on that table, it subtracts 44 or 22, depending on the status.