Sum values of multiple columns per row with the option “switch values to rows” turned on.

[Alex777]

Dear Community,

I have a similar problem as here (https://community.powerbi.com/t5/Desktop/Sum-values-of-multiple-columns-per-row/m-p/356026 

) with the difference that the values are switched to rows in the table in Power BI report. Please see the screenshot below.

Sample data 

Year	A	B	C
2020	1	2	3
2021	4	5	6
2021	1	2	3
2019	2	3	5
2018	5	6	7

Visual settings

How can I get a new column 2 years (2020+2021)? A table in Power BI contains of course much more columns as in the screenshot.

Thanks a lot in advance.

[SpartaBI · Accepted solution]

@Alex777 your source data is really not shaped in a good way to do stuff in the model.
I suggest first do this in Power Query:

to get this:

Than I would just create different measure for each year:
2018 measure = CALCULATE(sum(table[value]), table[year] = 2018)
.
.

.

.
2 years measure = CALCULATE(sum(table[value]), table[year] IN (2021,2022))

[Syndicate_Admin]

so that the measurements are dynamic according to filters, it is advisable to adjust the model as indicated by @SpartaBI. it would be time to redo the report, yes. but all calculations will be easier to design.

[Alex777]

It was amazing if it works by clicking on the required columns and then summarize in the menu

But it's greyed out...

[SpartaBI]

That not what this button do 🙂

It creates a text summary box of the visual.

I really suggest you will re structure your model. This invest will return it's cost 🙂

[SpartaBI]

If it's calculated column you want just write 2 years = 'table'[2020] + 'table' [2021]

[Alex777]

I can't do that, because there are no 2020 and 2021 columns in the source table, there is a column "year" with values 2020, 2021, ... Or have I misunderstood something?

[SpartaBI]

@Alex777 what exactly is your current table in Power BI and what do you want to make of it? 
Better send the current table as an example with some rows from excel and copy paste here.
Also, you want it as a new table in the model or as a visual in Power BI?  

[Alex777]

Unfotrunately I can't upload a pbix file, but I made some new screenshots, hope it helps to understand what I mean. You could copy the data from the table above and make a matrix table in power bi with the option as shown in the screenshot. All what I want to get a new column "2 years" in the visual in Power BI. 
"Show totals" does not fit, because I do not need all years from my table.

[SpartaBI · Accepted solution]

@Alex777 your source data is really not shaped in a good way to do stuff in the model.
I suggest first do this in Power Query:

to get this:

Than I would just create different measure for each year:
2018 measure = CALCULATE(sum(table[value]), table[year] = 2018)
.
.

.

.
2 years measure = CALCULATE(sum(table[value]), table[year] IN (2021,2022))

[Alex777]

In this case I have another problem 🙂
I have a lot of filtered values

I have extended the data model with the attribute account for tests:

Year	A	B	C	Account
2020	1	2	3	AAA
2021	4	5	6	AAA
2021	1	2	3	CCC
2019	2	3	5	AAA
2018	5	6	7	DDD

The "calculate" function does not seem to be working with these filtered values:

As I undestand I have to define all filters in 2years measure like: 

But in this case I don't know how to put 2years to columns in the table visual, it just doesn't work. I can only put it to rows.

[SpartaBI]

@Alex777 You will need to create a disconnected table with all the years as texts + an artificial value called "2 Years". You will need a sort by column. You will put this column in the rows. Then you will need to create a measure that checks what is the selected value and with let's say a switch function return the required result

It's all possible but I think too early at your journey.

That's why I suggested you create a more proper model so you won't need to do all these steps.

Let's wait if other community members can also suggest something else.

[Alex777]

I mean, I'm testing it with the model you suggested. And I can't put the new measure 2 years to columns.

[SpartaBI]

@Alex777 where did the column with "AAA" came from??? It wasn't in the original post. 
Please look again at the long message I wrote in the beginning. Do those steps:

You need to get to this table.

[Alex777]

AAA comes from the new attribute, that I added for tests. Have you seen my post above?

"I have extended the data model with the attribute "account" for tests: ..."

As I said above, I have changed a data model as you said (and delete the new attibute).

The question is how can I get the "2 years" measure to columns? It works only for rows:

Thanks a lot for your replies and your time!!

[SpartaBI · Accepted solution]

@Alex777 
Create these measures:
SumValue = sum(table[value])
2018 Value = CALCULATE([SumValue]), table[year] = 2018
2019 Value = CALCULATE([SumValue]), table[year] = 2018
2019 Value = CALCULATE([SumValue]), table[year] = 2018
2 years Value = CALCULATE([SumValue]), table[year] IN (2021,2022)).
Put the attribute column on the rows of the matrix and these measure as values.

In case it answered your question please mark this as a solution for community visibility. Appreciate your Kudos

[Alex777]

Thanks, it works with test data.
Unfortunately, the source table has been changed. It now contains one amount and several attributes, like 

Year	Atr_1	Atr_2	Atr_3	Atr_4	Atr_5	Atr_12	Amount
2018	a	s	d	f	g	h	1
2018	y	x	c	v	b	n	2
2019	a	s	d	f	g	h	3
2019	y	x	c	v	b	n	4
2020	a	s	d	f	g	h	5

The goal is the same.

	2017	2018	2019	2020	2021	2020+2021
Measure1
Measure2
…
Measure15

The Power BI report covers a period of about 10 years and contains ~15 measures like: 

Measure1 = CALCULATE(Sum(Amount), table[Atr_1] IN {s,d})

Measure2 = CALCULATE(Sum(Amount), table[Atr_2] IN {c,v})

Measure3 = Measure1+ Measure2

…

Measure15 = Measure12+Measure10-Measure8 (This is of course an example)

As I understand I should create for each year and each measure a new measure to get the measure "2 years"? It would be 10 years x 15 measures =150 new measures. Or have I misunderstood? Is there a way to define a measure once for all years? 

How can I reach the goal with this data model and with these conditions?

Should I mark this topic as solved and create a new topic so it would be more readable for the others?

[SpartaBI]

@Alex777 yes please. This is the right way to do, to make it more easy for other community members to find it more quickly.

I won't be near PC in the next half day at least , but I'm sure your new question will be taken care of by one of the many great helpers here

[Alex777]

Thanks a lot for your answer. But I would be very reluctant to change the data model, as the report already contains several pages with multiple visuals and multiple derived metrics. If I change the data model, I have to completely rework the report, practically recreate it. 
Is there perhaps a way to do this without big changing the data model (add new columns is no problem)? 
Thanks again for your help!