How sum of two variables from an excel spreadsheet

[nor303]

Hi

Im new to Power BI and Im trying to sum two variables from an excel spreadsheet and call it something else. I want to sum A and C and call it G, and Sum B and E and call M for example. Is it possible to do this in the desk top without creating a new column in the data set? 

Thanks in advance!

A	1000
B	1500
C	600
D	560
E	800
F	750

[PhilipTreacy]

Hi @nor303 

You can define names in the Excel workbook for your variables.  For example A_ and C_ for the cells where the corresponding values reside.

In Power Query, create a blank query and call it G.

open the Advanced Editor and use this code for this G query 

let
    A_ = Excel.CurrentWorkbook(){[Name="A_"]}[Content]{0}[Column1],
    C_ = Excel.CurrentWorkbook(){[Name="C_"]}[Content]{0}[Column1],
    Source = A_ + C_
in
    Source

The result of the query is 

Repeat this process for the other values/names you want to do calculations with.

The results of these queries can be used in other queries by using the query name, e.g.

let
    Source = G / 20
in
    Source

which gives the answer 80.

Regards

Phil

[nor303]

Thanks for your help! But not really the solution I was looking for. I was hoping that it was possible to make the formula within the desktop without making an extra column or shet. The reason is that next time I load a new updatet data set I was hoping that the formula automatically would do the same again.

[Dhairya]

Here we haven't added any new column, it's a measure which will automatically calculate the value when new data is added.
Still if you are not clear can you please share expected output?

[Dhairya]

Hey @nor303 
According to my understanding,
You have to first transpose this table in power query editor, which will look something like this

Then create following two Measures

G = SUM(Data[A])+SUM(Data[C])

M = SUM(Data[B])+SUM(Data[E])

After plotting all your columns and newly created measures in table visuals you will get following output

If it helps you please mark my solution as accepted.