calculated field

[jeoosma]

Hi. I don't know if it is possible make what I want. In a page I have two table which origin is two different table. The two table have some rows and the total. I would create a field that has the difference of the two total. How can I resolve? Thank you.

PS: Sory for my english, I'm from Italy.....

[v-yuezhe-msft · Accepted solution]

@jeoosma,

You screenshot is not clear.

Please try the DAX below.

TableDifference = CALCULATE(SUM(CdmEtZKenR[Importo]);ALL(CdmEtZKenR)) - CALCULATE(SUM('CdmEtZKenR(2)'[Importo]);ALL('CdmEtZKenR(2)'))

Regards,
Lydia

[deepak91g]

The best way is to use DAX.  You can refere below post  to refer a solution posted by Eric_Zhang

---

---

http://community.powerbi.com/t5/Desktop/Create-a-Calculated-Column-from-Different-Tables/td-p/60453

---

---

---

P.S Accept this as solution if your query is solved

[jeoosma]

What I would is to create a field with difference between the total 1 and total 2.

[Greg_Deckler]

TableDifference = CALCULATE([Importo],ALL(Table1)) - CALCULATE([Importo],ALL(Table2))

Not sure what the table names are for Table1 and Table2 but substitute the right table names.

[jeoosma]

?????? what am I doing wrong?

[Greg_Deckler]

You are in the Query Editor, you need to be in the Model in Desktop.

Also, if Importo is not a measure, you will need an aggregator (SUM):

TableDifference = CALCULATE(SUM([SomeMeasure]),ALL(Table1)) - CALCULATE(SUM([SomeOtherMeasure]),ALL(Table2))

[jeoosma]

I keep getting something wrong

[jthomson]

As smoupre says, if it's not a measure (which it isn't, you're just referencing a column) you need to sum over the column

[jeoosma]

The "Importo" field is the result of the test on  the "RechArt" value which defines the signe (positive or negative) of the ReBetrag value (in the CdmEtZKonR table). The same I do in the other table CdeAuftrag. So I have two field "Importo" from two table. In model section Smoupre says to insert the measure (how and where?) but I reveice an error (see the images I posted). 

[jthomson]

You can't insert the measure because you don't have a measure, either copy the DAX that's in post 7 (replacing Importo where he lists SomeMeasure) or make a measure that sums that column

[jeoosma]

Sorry, I'm not able to understand how resolve the problem. I will try to solve in less complicated way. Thank you.

[v-yuezhe-msft]

@jeoosma,

Right-click your table and choose "New Measure", then apply the following DAX.

TableDifference = CALCULATE(SUM(CdmEtZKenR[Importo]),ALL(CdmEtZKenR)) - CALCULATE(SUM('CdmEtZKenR(2)'[Importo]),ALL('CdmEtZKenR(2)'))

Regards,
Lydia

[jeoosma]

Hi Lidia thank you for reply. I insert the string you send me (I have modified the second table and the name) in his way:

• TableDifference = CALCULATE(SUM(CdmEtZKonR[Importo]),ALL(CdmEtZKonR)) - CALCULATE(SUM(‘CdeAuftragk(2)'[Importo]),ALL(' CdeAuftragk(2)’))

I receive this error:

The following syntax error occurred during parsing: Invalid token, Line 1, Offset 69, ‘.

Can you control the string? Thank you.

[v-yuezhe-msft · Accepted solution]

@jeoosma,

You screenshot is not clear.

Please try the DAX below.

TableDifference = CALCULATE(SUM(CdmEtZKenR[Importo]);ALL(CdmEtZKenR)) - CALCULATE(SUM('CdmEtZKenR(2)'[Importo]);ALL('CdmEtZKenR(2)'))

Regards,
Lydia

[jeoosma]

Hi Lydia. My problem is to have in the report the difference between the two total. In report I have the  table "ACQUISTI" (n english what I buy) and the table "VENDITE" (what I sell) with the total of the rows. I would have the difference between the two total. The first table is named "CdmEtKonR" and the second is named "CdeAuftragK".

In everyone there is the field "Importo" which is calculated with a condition because the value is positive or negative based on the value in other filed (in the firts table the command is 

= Table.AddColumn(#"Removed Columns", "Importo", each if [RechArt] ="1" then [ReBetragSum] else if [RechArt] ="2" then [ReBetragSum]*-1 else null)

In the second table is

= Table.AddColumn(#"Filtered Rows", "Importo", each if [RechnungsArt]="1" then [BetragNetto] else if [RechnungsArt] ="2" then [BetragNetto]*-1 else null)

I try your DAX in every way (first I modify the name CdmEtZKenR in CdmEtZKonR, after I have changed the name of the second table CdmEtZenR (2) in CdeAuftragk (2)) putting in the first table the new measure but the message I receive is:

The following syntax error occurred during parsing: Invalid token, Line 1, Offset 69, ‘.

PS: I upload the file in my drive if you want see it.

https://drive.google.com/open?id=1xUvEffRJbQWuSmY_hT2UK9pDnViofxLK

Thank you. Max.

[jthomson]

See the red squiggle in the latest image you posted? That's your clue, for one your table name looks to have a space in it whereas your DAX statement doesn't, it then has an unnecessary space in your ALL statement

[jeoosma]

Hi guy. I renamed the table to eliminate al space in the name. Now I have the table "ACQUISTI" and "VENDITE". The DAX command hasn't error but the value is not correct. Ishould have a difference 537179,78-674045,33= -133865,55 while in the report I have -57147322,46. There is something in the measure that doesn't work well.

[jthomson · Accepted solution]

It's probably a filter issue, I can see you're trying to filter on just 2018 on that visual but we have no idea what you're doing on the other visuals, that we're using ALL is going to override it

[jeoosma]

Thank you jthomson. You're right. I put the filter on the 2018 in the table and now the measure works correctly. Thanks all for support

[Greg_Deckler]

So, the Total line in a table/matrix is whatever calculation is occurring with a filter of ALL. Therefore, you should be able to wrap your calculation in an ALL filter to get the same result that is being shown in your table Total line. Thus, if you want to subtract, you could create a measure like:

TableDifference = CALCULATE([SomeMeasure],ALL(Table1)) - CALCULATE([SomeOtherMeasure],ALL(Table2))