Forum Discussion
Running Total with categories
Anonymous
Try replace ALLSELECTED('Data') to All(Data).
If not working, try :
RunningTotal = CALCULATE(SUM('Data'[Power]]);FILTER(ALL('DATA'); SUMX(FILTER('DATA';EARLIER('DATA'[Op_Year])<='DATA'[Op_year);1)))
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous thank you for the input.
Again not working, unfortuntely. The output is the one below:If it may help, below the raw data I am using for this example (the real db is much more bigger and confidential, but the below is fully representative of the problem)
SpoilerID Power Geo Status Op_Year 1 10 RoW Installation 2021 2 5 RoW Operational 2019 3 4 Europe Design 2021 4 7 Asia Operational 1996 5 10 Europe Operational 1993 6 3 Americas Installation 2021 7 5 Asia Operational 2000 8 3 RoW Operational 1990 9 7 Europe Design 2024 10 1 Europe Operational 2006 11 1 RoW Design 2025 12 4 Asia Operational 1999 13 1 RoW Operational 2016 14 6 Asia Installation 2021 15 7 RoW Operational 2015 16 6 RoW Operational 2009 17 3 RoW Operational 2016 18 3 Europe Operational 1997 19 8 RoW Operational 2001 20 4 Europe Operational 1994 21 5 RoW Installation 2022 22 2 Europe Operational 2004 23 7 RoW Operational 2012 24 1 Americas Operational 2017 25 9 Asia Design 2024 26 4 Asia Operational 2018 27 2 RoW Installation 2023 28 7 RoW Operational 2015 29 5 Asia Operational 2017 30 3 Americas Installation 2020 31 4 Europe Design 2028 32 6 Asia Operational 2019 33 8 RoW Operational 2017 34 10 Europe Operational 1993 35 9 Asia Operational 2017 36 5 RoW Design 2023 37 9 RoW Operational 2019 38 2 Asia Design 2024 39 3 Asia Operational 2013 40 3 Asia Operational 2017 41 7 Americas Operational 1997 42 6 Americas Design 2027 43 5 RoW Operational 2002 44 5 Americas Operational 2018 45 8 Americas Design 2022 46 6 Europe Operational 2008 47 2 RoW Operational 2015 48 4 Americas Operational 2011 49 6 Asia Operational 2017 50 7 Asia Operational 2000 Can anyone help on this?
Thanks! 😊Anonymous
With the current data your have, it is not possible to achieve your expected bar chart. The chart does not show data for other [Geo] (e.g.year 2020, 2023) is because you have no data to record the power of Year 2020 for [Geo] other than Americas, so you can not use the Year column show the value of that year.
To make it clear, I create a pibx with 3 additional rows for each Geo of year 2020. (no value).
In my opinion, a line chart is more appropriate to see the trend with your current data. Or you can just edit your original data by adding rows to fill the blanks for years with no value like the 3 rows I added in the pbix.
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous thank you very much for your input and for letting me understand the limits of the visuals.
Line charts can be a solution even though the still keep some limitations.
I was just wondering if there is a way to automatically create a new table with Years as rows and Geo as columns summing up the values of the previous table without any particular input but just with DAX code. This way it should work and also the limit of the line chart (values are "interrupted" at a specific year if there is no further input for that Geo for a following year) can be overcome.
Can you help on this please - if it is feasible?
Many thanks in advance!