cancel
Showing results for
Did you mean:
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:

 HTR DATE Headcount H 9/1/2019 2 H 9/2/2019 3 H 9/3/2019 4 H 9/4/2019 5 H 9/5/2019 6 H 9/6/2019 7 H 9/7/2019 8 H 9/8/2019 9 H 9/9/2019 1 M 9/14/2019 3 T 9/15/2019 2 M 10/29/2029 2 M 9/10/2019 1 M 9/11/2019 2 M 9/21/2019 2 T 9/22/2019 2 T 10/25/2029 2 T 9/24/2019 2 T 9/10/2019 2 T 9/11/2019 2 H 9/1/2019 30 M 9/1/2019 20 T 9/1/2019 40

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 powerbi

Desired Output:

 Date H M T Grand Total 9/1/2019 32 20 40 92 9/2/2019 35 20 40 95 9/3/2019 39 20 40 99 9/4/2019 44 20 40 104 9/5/2019 50 20 40 110 9/6/2019 57 20 40 117 9/7/2019 65 20 40 125 9/8/2019 74 20 40 134 9/9/2019 75 20 40 135 9/10/2019 75 21 42 138 9/11/2019 75 23 44 142 9/14/2019 75 26 44 145 9/15/2019 75 26 46 147 9/21/2019 75 28 46 149 9/22/2019 75 28 48 151 9/24/2019 75 28 50 153 10/29/2029 75 30 50 155 10/25/2029 75 30 52 157

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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(
FILTER(ALL(Table[Date]),Table[Date] <= MAX(Table[Date])),Table[HTR]="H"
)

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

T = CALCULATE(
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/

Highlighted
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(
FILTER(ALL(Table[Date]),Table[Date] <= MAX(Table[Date])),Table[HTR]="H"
)

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

T = CALCULATE(
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/

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors