Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative data

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

YearQuarterSum of valueMetrics
2023Qtr 3649Hybrid
2023Qtr 4882Hybrid
2024Qtr 12293Hybrid
2024Qtr 23758Hybrid
2024Qtr 35700Hybrid
2024Qtr 48353Hybrid
2025Qtr 112065Hybrid
2025Qtr 216473Hybrid
2025Qtr 321243Hybrid
2023Qtr 422197Trucks
2025Qtr 425910Hybrid
2023Qtr 336943Trucks
2024Qtr 248454Trucks
2024Qtr 153045Trucks
2023Qtr 272339Trucks
2023Qtr 1119414Trucks
2022Qtr 4172165Trucks
2022Qtr 3225002Trucks
2022Qtr 2279420Trucks
2022Qtr 1336722Trucks
2021Qtr 4400994Trucks
2021Qtr 3474684Trucks
2021Qtr 2555443Trucks
2021Qtr 1639901Trucks
2019Qtr 1665763Cars
2019Qtr 2724065Cars
2020Qtr 4778077Trucks
2019Qtr 3783660Cars
2019Qtr 4844790Cars
2020Qtr 2891769Trucks
2020Qtr 3898959Trucks
2020Qtr 1900637Cars
2020Qtr 1922212Trucks
2020Qtr 2969917Cars
2020Qtr 31034333Cars
2019Qtr 41082718Trucks
2020Qtr 41101238Cars
2021Qtr 11171442Cars
2019Qtr 21213649Trucks
2019Qtr 31217490Trucks
2021Qtr 21243867Cars
2019Qtr 11253857Trucks
2021Qtr 31319714Cars
2021Qtr 41397898Cars
2022Qtr 11476404Cars
2025Qtr 41538678Buses
2022Qtr 21552369Cars
2025Qtr 31574382Buses
2025Qtr 21619551Buses
2022Qtr 31627190Cars
2025Qtr 11670737Buses
2022Qtr 41699629Cars
2024Qtr 41726510Buses
2023Qtr 11771214Cars
2024Qtr 31784921Buses
2024Qtr 21824985Buses
2023Qtr 21842956Cars
2024Qtr 11885126Buses
2019Qtr 11889090Buses
2023Qtr 31915784Cars
2019Qtr 21928423Buses
2023Qtr 41951878Buses
2019Qtr 31966417Buses
2023Qtr 41989998Cars
2019Qtr 42002170Buses
2023Qtr 32003525Buses
2020Qtr 12019433Buses
2024Qtr 12033194Cars
2023Qtr 22034660Buses
2023Qtr 12052485Buses
2020Qtr 22061946Buses
2022Qtr 42064107Buses
2022Qtr 32075240Buses
2020Qtr 32084373Buses
2022Qtr 22086643Buses
2022Qtr 12097215Buses
2020Qtr 42100980Buses
2024Qtr 22105006Cars
2021Qtr 42106177Buses
2021Qtr 12110673Buses
2021Qtr 32112136Buses
2021Qtr 22113954Buses
2024Qtr 32199873Cars
2024Qtr 42264265Cars
2025Qtr 12325372Cars
2025Qtr 22381568Cars
2025Qtr 32432237Cars
2025Qtr 42474562Cars

 

 

 

DAX
  • Anonymous's avatar
    Anonymous
    6 years ago

    Deepak,

    That's amazing,

    Thanks a lot.

    Best

    Q

5 Replies

  • deepak91g's avatar
    deepak91g
    Resolver II
    6 years ago

     

     

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

       

      • deepak91g's avatar
        deepak91g
        Resolver II
        6 years ago

        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)