Sum cumulative measure at the top of the graph

[Anonymous]

Hello

I have posted my question in the spanish forum and now I cannt delete it from there, can the admin remove it from https://community.powerbi.com/t5/Desktop/sum-of-running-total-on-top-of-the-Line-and-Stacked-column-... please?

I need to display the sum total of a cumulative measure at the top of the graph. I am using Line and Stacked column chart and the running total is calculated by

Cumalative Cash Flows = CALCULATE (sum ('sample datatype' [Cash Flow]),

filter(

ALLSELECTED ('sample datatype'),

'sample datatype' [Financial Year] <= Max ('sample datatype' [Financial Year])

)

)

As shown above, the line values ​​are showing in the wrong place with the wrong values, how can I fix this?

Thank you

I do not think a sample data is required, however, the below table shows a sample data

Asset type	Cash Flow	Financial Year
1	100	2020
1	200	2021
1	300	2022
1	400	2023
1	500	2024
1	600	2025
1	700	2026
2	800	2020
2	900	2021
2	1000	2022
2	1100	2023
2	1200	2024
2	1300	2025
2	1400	2026
3	1500	2020
3	1600	2021
3	1700	2022
3	1800	2023
3	1900	2024
3	2000	2025
3	2100	2026
4	2200	2020
4	2300	2021
4	2400	2022
4	2500	2023
4	2600	2024
4	2700	2025
4	2800	2026
5	2900	2020
5	3000	2021
5	3100	2022
5	3200	2023
5	3300	2024
5	3400	2025
5	3500	2026

[mahoneypat · Accepted solution]

You can't use the same measurement for both of us. It actually works for your series of lines and is the column that is incorrect. Please try this for column one, and use an existing one for the line.

Cumalative Cash Flows - CALCULATE (sum ('sample data type' [Cash Flow]),

filter(

ALLSELECTED ('sample data type'[Financial Year]),

'sample data type' [Financial Year] <- Max ('sample data type' [Financial Year])

)

)

If this works for you, mark it as the solution. Praise is also appreciated. Please let me know if you don't.

Best regards

Pat

[Anonymous]

@mahoneypat

Thanks, your instinct is right, I need two formulas: one for line and one for column,

The one that we identified only works for the line and gives incorrect values for the cumulative of columns.

It works fine on the sample data as I think there are not many filters there, but it gives me incorrect values on my actual dataset.

I will try to replicate the issue with a sample data as my data is sensitive and cannot upload it there. I will ask another question about this new issue.

[Anonymous]

@mahoneypat 

I am using this formula for both and it seems it is working fine, I doubled checked the values manually, are you sure there is a need for two measures?

[mahoneypat]

Maybe I missed something but I needed to do that with your sample data to get this output.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mhossain]

@Anonymous 

Please try this:

Cumalative Cash Flows = CALCULATE (sum ('sample datatype' [Cash Flow]),

filter(

ALL ('sample datatype'),

'sample datatype' [Financial Year] <= Max ('sample datatype' [Financial Year])

)

)

[Anonymous]

This does not work as supposed to, All disables all the visual filters and it will give me wrong answer if users start playing with filters

[mhossain]

@Sichanm

try with All(Table[date column])

[Anonymous]

@mhossain 

Can you please explain why are you keep suggesting all and not allselected,

and

why you are sugging also putting a single column in the all while they may be other filters in the report

Based on my test the below works fine, got the idea from you, but still looking for the above questions

CALCULATE( sum(Output_SSIS[Cash Flow]),filter(ALLSELECTED(Output_SSIS[Financial Year]),Output_SSIS[Financial Year]<=Max(Output_SSIS[Financial Year])))

Not the all

[mhossain]

@Anonymous 

Suggested ignoring date column filter bcoz in the charts in axis was trying to ignore the year filter because we require to ignore the year filter and sum the value at total level.

Sorry for any confusion, your formula looks fine now.