Show sum of values in a year as a constant for all years in a table

[Anonymous]

Hello, I am trying to create a measure that calculates the sum of all values for a specific year as a constant and then report that number in every single row, regardless of the filters and slicers used in the workbook. Below is a table with an example of what I need (2019 SUM)

Year and Month come from "Calendar" table, Color comes from "Color" table and Value comes from "Value" table, the table shown below is the result of putting the different measures together in a visualization , and I need to obtain the red column (final).

Year	Month	Color	Value	2019 SUM
2019	Jun	blue	0.550	4.795
2019	Jul	red	0.780	4.795
2019	Aug	orange	0.998	4.795
2019	Sep	white	0.875	4.795
2019	Oct	black	0.654	4.795
2019	Nov	purple	0.938	4.795
2020	Jun	green	0.637	4.795
2020	Jul	pink	0.363	4.795
2020	Aug	black	0.498	4.795
2020	Sep	gray	0.877	4.795
2020	Oct	magenta	0.987	4.795
2020	Nov	yellow	0.543	4.795
2021	Jun	silver	0.789	4.795
2021	Jul	gold	0.367	4.795
2021	Aug	bronce	0.467	4.795
2021	Sep	coal	0.984	4.795
2021	Oct	chocolate	0.763	4.795
2021	Nov	brown	0.777	4.795

My main problem is that the data is under live connection so I'm not able to creat tables, nor import files, queries, or even manage relationships so my options are limited and I can only create measures.

I have tried a query using VAR "..."= calculate()....... RETURN..... and sumx(summarize()) but I'm not able to get the result I need.

[Anonymous]

How do I add a switch? let's say if I wanted to calculate that difference for all years and the ones to come? Since this is a live connection file, it is updated monthly and the idea is for it to work in future years.

[Anonymous]

Hello, it did work, however, when I try to build another table using another year (2018) the final table considers absolutely all values instead of comparing just 2018 values with the 2019 values, please picture below.

Year	Month	Color	Value	FY19 SUM	Difference
2018	Jun	blue		4.795	-4.795
2018	Jul	red		4.795	-4.795
2018	Aug	orange		4.795	-4.795
2018	Sep	white		4.795	-4.795
2018	Oct	black		4.795	-4.795
2018	Nov	purple		4.795	-4.795
2018	Jun	Indigo	0.665	4.795	-4.130
2018	Jul	Pink	0.778	4.795	-4.017
2018	Aug	Cyan	0.888	4.795	-3.907
2018	Sep	Dark	0.909	4.795	-3.886
2018	Oct	Light	0.987	4.795	-3.808
2018	Nov	lime	0.889	4.795	-3.906

When I try to do the difference between the values of colors in 2018 minus the 2019 value, I get the wrong table, I just need to see the 2018 difference, not all of them.

Eventually, I am going to use this difference to count how many colors in 2018 where below 2019, and then a % which I can chart by month.

[Nathaniel_C]

Hi @Anonymous ,
Not sure if this what you would like, but it should move you along the path.

Either 2019 or 2018 =
var _curYear = MAX(Table2[Year])

var _20calc= CALCULATE([Total Values], All(Table2),Table2[Year]=2020)
var _19calc =CALCULATE([Total Values], All(Table2),Table2[Year]=2019)

Return If (_curYear = 2019, _19calc, If(_curYear =2020, _20calc)) // or to do all years you can add a switch)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @Anonymous ,
Here is your measure 

Sum of 2019 values = CALCULATE([Total Values], All(Table2),Table2[Year]=2019)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[mahoneypat]

Please try this measure expression, assuming your Year column is an integer.  If not, add double quotes around 2019.

2019 Sum = CALCULATE(SUM(Value[Value]), ALL('Calendar'), ALL(Color[Color]), 'Calendar'[Year] = 2019)

Regards,

Pat