Forum Discussion

jeoosma's avatar
jeoosma
Helper II
8 years ago
Solved

calculated field

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 di...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

  • jthomson's avatar
    jthomson
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
jeoosma
Helper II
8 years ago

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's avatar
    jthomson
    Solution Sage
    8 years ago

    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's avatar
    jeoosma
    Helper II
    8 years ago

    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:smileyhappy:

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    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's avatar
    jeoosma
    Helper II
    8 years ago

    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.