cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Need Help in getting the running total quick measure

Team, I am using running total from quick measures and not getting the desired output.. Given my dataset below, Headcount should be cumulative total of HTR column based on dates..

Data set:

HTRDATEHeadcount
H9/1/20192
H9/2/20193
H9/3/20194
H9/4/20195
H9/5/20196
H9/6/20197
H9/7/20198
H9/8/20199
H9/9/20191
M9/14/20193
T9/15/20192
M10/29/20292
M9/10/20191
M9/11/20192
M9/21/20192
T9/22/20192
T10/25/20292
T9/24/20192
T9/10/20192
T9/11/20192
H9/1/201930
M9/1/201920
T9/1/201940

 

Output am getting:  Only Total is getting computed correctly.. but the column values are not correct as is total value present in that particular date..

Output in powerbiOutput in powerbi

Desired Output: 

 

DateHMTGrand Total
9/1/201932204092
9/2/201935204095
9/3/201939204099
9/4/2019442040104
9/5/2019502040110
9/6/2019572040117
9/7/2019652040125
9/8/2019742040134
9/9/2019752040135
9/10/2019752142138
9/11/2019752344142
9/14/2019752644145
9/15/2019752646147
9/21/2019752846149
9/22/2019752848151
9/24/2019752850153
10/29/2029753050155
10/25/2029753052157

 

How to acheive this... Help needed!!!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Hi.

To achieve this you can create measures for the running totals and filter them by the different HTR categories. 

1. Create the three measures as described below

H = CALCULATE(
        SUM(Table[Headcount]),
        FILTER(ALL(Table[Date]),Table[Date] <= MAX(Table[Date])),Table[HTR]="H"
)

M = CALCULATE(
        SUM(Table[Headcount]),
        FILTER(ALL(Table[Date]),Table[Date] <= MAX(Table[Date])),Table[HTR]="M"
)

T = CALCULATE(
        SUM(Table[Headcount]),
        FILTER(ALL(Table[Date]),Table[Date] <= MAX(Table[Date])),Table[HTR]="T"
)

2. Create a matrix-visual and add the date fields to the rows area and the measures [H], [M], [T] and your [Grand Total]-measure to the values area for the visual.

You can find more information about cumulative totals here: https://www.daxpatterns.com/cumulative-total/

View solution in original post

1 REPLY 1
Highlighted
Helper I
Helper I

Hi.

To achieve this you can create measures for the running totals and filter them by the different HTR categories. 

1. Create the three measures as described below

H = CALCULATE(
        SUM(Table[Headcount]),
        FILTER(ALL(Table[Date]),Table[Date] <= MAX(Table[Date])),Table[HTR]="H"
)

M = CALCULATE(
        SUM(Table[Headcount]),
        FILTER(ALL(Table[Date]),Table[Date] <= MAX(Table[Date])),Table[HTR]="M"
)

T = CALCULATE(
        SUM(Table[Headcount]),
        FILTER(ALL(Table[Date]),Table[Date] <= MAX(Table[Date])),Table[HTR]="T"
)

2. Create a matrix-visual and add the date fields to the rows area and the measures [H], [M], [T] and your [Grand Total]-measure to the values area for the visual.

You can find more information about cumulative totals here: https://www.daxpatterns.com/cumulative-total/

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors