Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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..
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!!!!!
Solved! Go to Solution.
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/
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |