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.

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors