Running Total with categories

[Anonymous]

Hi all, I have a question on running totals:
I have a table "Data" of projects (identified by ID). Each project has a generated power "Power", an operational start year "Op_Year" (in number format - not date), a current status (e.g. design, under construction, operative) "Status" and a country "Geo".

I successfully plotted in a stacked bar chart the additional power that will be available from 2020 to 2025 by Geo and in another by Status and I would like now to create other two bar charts with the cumulative installed power (also considering power already installed before 2020 but start plotting from 2020 onward). 

I used the following measure that worked to plot the cumulative power:

RunningTotal = CALCULATE(SUM('Data'[Power]]);FILTER(ALLSELECTED('Data');'Data'[Op_Year]<=MAX('Data'[Op_Year])))

(with minimum x-axis value 2020 and max 2025 to limit the years as needed).

But when I try to add a legend (Geo or Status) to the chart it does not work anymore (it even changes values and totals in the chart)! I also tried to modify the formula this way but again it did not work correctly (same for Status instead of Geo).

RunningTotal = CALCULATE(SUM('Data'[Power]]);FILTER(ALLSELECTED('Data');'Data'[Op_Year]<=MAX('Data'[Op_Year])&&'Data'[Geo]=MAX('Data'[Geo])))

Can you please help me to understand where I am mistaking? Thanks!

[PavanLalwani]

The issue you’re facing with your cumulative power measure when adding a legend (such as "Geo" or "Status") is likely due to how the DAX formula interacts with the context of the visualization. When you add a legend, it creates additional filtering contexts that affect the calculation, so it’s essential to modify the formula to account for these contexts properly.

Here's a solution you can try by modifying the `RunningTotal` measure to take into account the legend (e.g., "Geo" or "Status"):

### Running Total with Categories Solution:

```DAX
RunningTotal =
VAR CurrentGeo = MAX('Data'[Geo]) -- or MAX('Data'[Status]) if using Status as the category
VAR CurrentYear = MAX('Data'[Op_Year])

RETURN
CALCULATE(
SUM('Data'[Power]),
FILTER(
ALLSELECTED('Data'),
'Data'[Op_Year] <= CurrentYear
&& 'Data'[Geo] = CurrentGeo -- or 'Data'[Status] = CurrentStatus] for Status
)
)
```

### Explanation:
1. **`CurrentGeo` and `CurrentYear`**: These variables capture the current category and year that the chart is focusing on, based on the selected data points.

2. **Filtering with Context**: The `FILTER` function now includes both the year and the `Geo` (or `Status`), ensuring that the cumulative total is calculated only for the current category being plotted.

3. **ALLSELECTED**: This function ensures that the running total respects the selections made in the report (e.g., if you have other slicers affecting the data).

### Notes:
- If you're using `Status` as the legend instead of `Geo`, replace `Geo` with `Status` in the formula.
- You can use this formula for both charts with different legends (one for `Geo` and one for `Status`) by adapting the variable `CurrentGeo` to the relevant column.

This should allow you to properly display the running totals for each category (either by "Geo" or "Status") in the chart without affecting the totals or changing values unexpectedly.

[Elvi_well]

Thanks for the explanation!
I have the issue that if there is no a category on the last period it does not show me from the previous period. So mark A from grade 2 is not showen on the grade 3. Is it possible to solve it somehow?

[Anonymous]

@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]

@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)

Spoiler

ID	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

[Anonymous]

@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).

pibx: https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EZXm-HhQM_1BrgwJ6ASMA3YBZptgqjHJxH05SQBQYMsR7Q?e=pjwJEr

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]

@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!

[Anonymous]

@Anonymous sorry to bother you on this, but do you see a way to proceed as described in my previous message?
Many thanks!

[Anonymous]

Can anyone help on this?

Thanks! 😊

[sturlaws]

Hi @Anonymous,

could you try changing your code to this:

RunningTotal =
CALCULATE (
    SUM ( 'Data'[Power] );
    FILTER (
        ALLEXCEPT ( 'Data'; Data[Geo] );
        'Data'[Op_Year] <= MAX ( 'Data'[Op_Year] )
    )
)

[Anonymous]

Hi @sturlaws, thanks for you reply.
Unfortunately it does not work . Until I try to categorize the thing it is working as expected (see picture).

 









When I add "Geo" to the legend of the chart, it changes to this:

While what I expect is something like this:

[Elvi_well]

Hi!
did you solved the problem? I have the same issue.