Forum Discussion
Another Running Total Question
OK turns out this was way easier than I was making it. Instead of messing with the formula and doing everything 'by force', there is a Quick Measure for Year to Date. I thought at the time I was playing with it that the YTD would only work for data in the current year, but it will actually work for multiple years. I was able to add a YTD version of the metric and it worked perfectly and gave me the exact graph I was looking for there.
Anonymous
Give it a shot
Metric Cumulative =
CALCULATE (
SUM ( 'Raw Data'[Component] ) / [Average Customers],
FILTER (
ALLSELECTED ( 'Raw Data' ),
'Raw Data'[Start Date] <= MAX ( 'Raw Data'[Start Date] )
&& YEAR ( 'Raw Data'[Start Date] )
= YEAR ( SELECTEDVALUE ( 'Raw Data'[Start Date] ) )
)
)Hm that didn't work, nothing is plotting at all. I tried adjusting the legend / axis settings but no dice. It's acting as if there are no records where that matches.
I tried setting it to <= and still nothing plotted, but when I set it to >= it plotted the same graph as before. It would need to be = but I don't know why it doesn't work.
Anonymous
Could you share your file?
Unfortunately no I can't, it has some sensitive data.
The format is something like this:
Customer Count Table:
Territory Customers Year Month YearMon A 37,381.00 2017 12 201712 B 47,416.00 2017 12 201712 C 10,106.00 2017 12 201712 D 109,685.00 2017 12 201712 E 64,144.00 2017 12 201712 F 9,551.00 2017 12 201712 G 152,264.00 2017 12 201712 H 61,934.00 2017 12 201712 I 20,968.00 2017 12 201712 J 10,536.00 2017 12 201712 K 14,323.00 2017 12 201712 L 7,801.00 2017 12 201712 M 101,184.00 2017 12 201712 N 30,221.00 2017 12 201712 O 17,931.00 2017 12 201712 P 15,918.00 2017 12 201712 Q 83,562.00 2017 12 201712 R 49,529.00 2017 12 201712 S 2,844.00 2017 12 201712 T 21,332.00 2017 12 201712 A 37,452.00 2018 1 20181 B 47,483.00 2018 1 20181 C 10,108.00 2018 1 20181 D 109,898.00 2018 1 20181 E 64,600.00 2018 1 20181 F 9,548.00 2018 1 20181 G 152,249.00 2018 1 20181 H 61,999.00 2018 1 20181 I 20,983.00 2018 1 20181 J 10,530.00 2018 1 20181 K 14,327.00 2018 1 20181 L 7,804.00 2018 1 20181 M 105,975.00 2018 1 20181 N 30,184.00 2018 1 20181 O 18,001.00 2018 1 20181 P 15,908.00 2018 1 20181 Q 83,549.00 2018 1 20181 R 49,557.00 2018 1 20181 S 2,835.00 2018 1 20181 T 21,348.00 2018 1 20181 A 10,512.00 2018 3 20183 B 14,583.00 2018 3 20183 C 7,795.00 2018 3 20183 D 105,777.00 2018 3 20183 E 30,154.00 2018 3 20183 F 18,058.00 2018 3 20183 G 15,895.00 2018 3 20183 H 83,514.00 2018 3 20183 I 49,475.00 2018 3 20183 J 2,826.00 2018 3 20183 K 21,353.00 2018 3 20183 L 37,498.00 2018 3 20183 M 47,551.00 2018 3 20183 N 10,104.00 2018 3 20183 O 109,784.00 2018 3 20183 P 64,638.00 2018 3 20183 Q 9,586.00 2018 3 20183 R 152,341.00 2018 3 20183 S 62,076.00 2018 3 20183 T 21,019.00 2018 3 20183
Event Table:
Event Table
Event Metric Component Date/Time Year-Mon 1 10 1/1/2018 5:30AM 2018-1 2 42 1/3/2018 5:30AM 2018-1 3 5431 2/1/2018 5:30AM 2018-2 4 249 2/1/2018 6:30AM 2018-2 My Average Customer formula is:
Average Customers Served = AVERAGEX( summarize('Customers by Center', 'Customers by Center'[Year], 'Customers by Center'[Month], "CustomSum", SUM('Customers by Center'[Customers]) ), [CustomSum] )The Metric (non cumulative version) is:
Metric = sum('Raw Data'[Component])/[Average Customers Served]And that works great, except that I'm wanting a graph that will show the month to month accumulation by year, and then I want to be able to drill into that line graph to see the day to day accumulation by month. I just can't get the Cumulative component to split based on year. The SelectedValue, do you think it's returning more than one element? Is that why it isn't returning anything at all when I specify <= or = year in the formula you provided? I think that methodology should work, but I'm not very familiar with how SelectedValue() works