Cumulative data

[Anonymous]

Hello,

I am struggling to visualise the values on a stack columns chart correctly for "Years".
I have a table with four columns; Year, Quarter, Model and Units (See the CSV file bellow).

When the chart is on the "Quarters" hierarchy, the Units values are showing correctly but when I use "drill up" arrow on the table, the Units values are wrong.

I guess the yearly values are cumulative and summing up and displaying the values of Q1, Q2, Q3 and Q4 for each year.
How can I Transform this table to show both quarterly and yearly values correctly?
Thanks for your time.

Best

Qmars

Year	Quarter	Sum of value	Metrics
2023	Qtr 3	649	Hybrid
2023	Qtr 4	882	Hybrid
2024	Qtr 1	2293	Hybrid
2024	Qtr 2	3758	Hybrid
2024	Qtr 3	5700	Hybrid
2024	Qtr 4	8353	Hybrid
2025	Qtr 1	12065	Hybrid
2025	Qtr 2	16473	Hybrid
2025	Qtr 3	21243	Hybrid
2023	Qtr 4	22197	Trucks
2025	Qtr 4	25910	Hybrid
2023	Qtr 3	36943	Trucks
2024	Qtr 2	48454	Trucks
2024	Qtr 1	53045	Trucks
2023	Qtr 2	72339	Trucks
2023	Qtr 1	119414	Trucks
2022	Qtr 4	172165	Trucks
2022	Qtr 3	225002	Trucks
2022	Qtr 2	279420	Trucks
2022	Qtr 1	336722	Trucks
2021	Qtr 4	400994	Trucks
2021	Qtr 3	474684	Trucks
2021	Qtr 2	555443	Trucks
2021	Qtr 1	639901	Trucks
2019	Qtr 1	665763	Cars
2019	Qtr 2	724065	Cars
2020	Qtr 4	778077	Trucks
2019	Qtr 3	783660	Cars
2019	Qtr 4	844790	Cars
2020	Qtr 2	891769	Trucks
2020	Qtr 3	898959	Trucks
2020	Qtr 1	900637	Cars
2020	Qtr 1	922212	Trucks
2020	Qtr 2	969917	Cars
2020	Qtr 3	1034333	Cars
2019	Qtr 4	1082718	Trucks
2020	Qtr 4	1101238	Cars
2021	Qtr 1	1171442	Cars
2019	Qtr 2	1213649	Trucks
2019	Qtr 3	1217490	Trucks
2021	Qtr 2	1243867	Cars
2019	Qtr 1	1253857	Trucks
2021	Qtr 3	1319714	Cars
2021	Qtr 4	1397898	Cars
2022	Qtr 1	1476404	Cars
2025	Qtr 4	1538678	Buses
2022	Qtr 2	1552369	Cars
2025	Qtr 3	1574382	Buses
2025	Qtr 2	1619551	Buses
2022	Qtr 3	1627190	Cars
2025	Qtr 1	1670737	Buses
2022	Qtr 4	1699629	Cars
2024	Qtr 4	1726510	Buses
2023	Qtr 1	1771214	Cars
2024	Qtr 3	1784921	Buses
2024	Qtr 2	1824985	Buses
2023	Qtr 2	1842956	Cars
2024	Qtr 1	1885126	Buses
2019	Qtr 1	1889090	Buses
2023	Qtr 3	1915784	Cars
2019	Qtr 2	1928423	Buses
2023	Qtr 4	1951878	Buses
2019	Qtr 3	1966417	Buses
2023	Qtr 4	1989998	Cars
2019	Qtr 4	2002170	Buses
2023	Qtr 3	2003525	Buses
2020	Qtr 1	2019433	Buses
2024	Qtr 1	2033194	Cars
2023	Qtr 2	2034660	Buses
2023	Qtr 1	2052485	Buses
2020	Qtr 2	2061946	Buses
2022	Qtr 4	2064107	Buses
2022	Qtr 3	2075240	Buses
2020	Qtr 3	2084373	Buses
2022	Qtr 2	2086643	Buses
2022	Qtr 1	2097215	Buses
2020	Qtr 4	2100980	Buses
2024	Qtr 2	2105006	Cars
2021	Qtr 4	2106177	Buses
2021	Qtr 1	2110673	Buses
2021	Qtr 3	2112136	Buses
2021	Qtr 2	2113954	Buses
2024	Qtr 3	2199873	Cars
2024	Qtr 4	2264265	Cars
2025	Qtr 1	2325372	Cars
2025	Qtr 2	2381568	Cars
2025	Qtr 3	2432237	Cars
2025	Qtr 4	2474562	Cars

[deepak91g]

Hi @Anonymous 

You need to create a hierarchy (Year and Quarter). After that, you will get the correct results. I have validated the results using the data provided by you above.

Thanks

Deepak

[Anonymous]

Hi Deepak,

Great effort but your solution is not working in the sense that the yearly value based on your solution is the summation of Q1, Q2, Q3 and Q4 (Cumulative).

I am looking for a formula which displays the value of Q4 of each year as the value of that given year.

For example, if the population of a country in Q1, Q2, Q3, and Q4 of 2020 is 1000, 2000, 3000, and 4000, the annual population is 4000 but 10000.

Hope this simple example describes what I need to do.

Best

Q

[Anonymous]

Hi,

I would recommend building a new measure using INSCOPE function to get the deisred resutls. The formula filters down the value to just Qtr 4 when not looking at the numebrs at Quarter level.

New Measure = IF(
NOT(ISINSCOPE(Sheet1[Quarter])),
CALCULATE(SUM(Sheet1[Sum of value]),FILTER(Sheet1,Sheet1[Quarter]="Qtr 4")),
SUM(Sheet1[Sum of value])
)

Sheet1 : name of Table

Note: You still ned to build the Year-Quarter Hierarchy

[deepak91g]

Hi @Anonymous 

I have created a Qtr column which contains the numerical value of each Qtr i.e Qtr 1 -> 1 or Qtr 2 -> 2 etc

Created a measure which will return closing value for each year

Total =

VAR Qtr = MAX(Chart[Qtr])

RETURN

CALCULATE(SUM(Chart[Sum of value]), Chart[Qtr] = Qtr)

[Anonymous · Accepted solution]

Deepak,

That's amazing,

Thanks a lot.

Best

Q