Column summarization on each row with department wise

[rajendraongole1]

Hi All,

I am actually doing the summation for row by row by using the column Current HC and dept flag but the result was coming unexpected. can you please suggest the expected output mentioned in trail mail. any help appreciate it. 

Dept Flag	Month	Current HC
Admin	6/1/2021	11
Admin	7/1/2021	11
Admin	8/1/2021	11
Admin	9/1/2021	11
FA	6/1/2021	69
FA	7/1/2021	69
FA	8/1/2021	69
FA	9/1/2021	69
Audit	6/1/2021	98
Audit	7/1/2021	98
Audit	8/1/2021	98
Audit	9/1/2021	98

Expected Output:

Dept Flag	Month	Current HC	Expected Output
Admin	6/1/2021	11	11
Admin	7/1/2021	11	22
Admin	8/1/2021	11	33
Admin	9/1/2021	11	44
FA	6/1/2021	69	69
FA	7/1/2021	69	138
FA	8/1/2021	69	207
FA	9/1/2021	69	276
Audit	6/1/2021	98	98
Audit	7/1/2021	98	196
Audit	8/1/2021	98	294
Audit	9/1/2021	98	392

Expected output column is coming summation for each department.

Regards,

Rajendra

[AlexisOlson · Accepted solution]

This is a cumulative total rather than a summarization.

See if this posts helps:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Cumulative-Sum-by-month-per-Product/m-p/10863...

[rajendraongole1]

Let me share the expected out put:

Below is the logic i have been used. it is deriving the cummulative sum but dept flag and date wise the date need to pick the initial value and need to perform the sum.

I have used the below logic in measure:

calculate ([total HC],FILTER(ALL(Table1),Table1[Dept Flag]<=max(Table1[Dept Flag])),FILTER(ALL(Table1),Table1[month]<=Table1[month]))))

[rajendraongole1]

Output result expecting :

cummulative summation based on dept and datewise.

Dept Flag	Month	Current HC	Expected Output
Admin	6/1/2021	11	11
Admin	7/1/2021	11	22
Admin	8/1/2021	11	33
Admin	9/1/2021	11	44
FA	6/1/2021	69	69
FA	7/1/2021	69	138
FA	8/1/2021	69	207
FA	9/1/2021	69	276
Audit	6/1/2021	98	98
Audit	7/1/2021	98	196
Audit	8/1/2021	98	294
Audit	9/1/2021	98	392

[speedramps]

Sorry I dont understand.

Please can you use the icon next to the [HTML] and copy and paste as a table rather than text,
and better explan the problem.

[speedramps]

Thank you rajendraongole1 for the better example.

It looks like you want a running total by Department

When you produce a table visual it creates default filters for each row, in this case product and date.

So you need to use a command  to get the end date of your current row.

Then remove the date filter but keep the department filter.

Then sum the amount for all the dates up to and inclusing the end date.

Here are 2 solutions ....

Running Total =

VAR enddate = MAX(Facts[Date])

RETURN

CALCULATE(

SUM(Facts[Amount]),

REMOVEFILTERS(Facts[Date]),

Facts[Date] <= enddate

)

Running Total =

VAR enddate = MAX(Facts[Date])

RETURN

CALCULATE(

SUM(Facts[Amount]),

ALL(Facts[Date]),

Facts[Date] <= enddate

)

Remember we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !