Forum Discussion

Raul's avatar
Raul
Post Patron
8 years ago
Solved

Total value for all rows

Hello, I have this matrix visualization with 2 measures:   AREA Concepte FACT Cost Fact TempCOST TempFACT Area1        1.437,25        2.880,19        1.437,25        2.880,19   ...
Need Help
  • Raul's avatar
    Raul
    8 years ago

    I've found the solution!!!

    The formula for the measure is:

    TempCOST = CALCULATE(SUM(Table1[Cost]);ALLSELECTED(Table1);VALUES(Table1[Area]))

     

    Thank you Anonymous, Pavlous and Floriankx for your help.

    Bye!!

Raul's avatar
Raul
Post Patron
8 years ago

Here you are:

 

DATECODAREACOLCONCEPTECOSTFACTCONCEPTE FAC
11/05/2017A1AM            10,00   AM
24/06/2017A1EC              5,00   EC
05/07/2017A2AM            12,00   AM
06/07/2017A3JS            11,00   JS
01/01/2017A1MP            25,00   MP
28/02/2017A2MR            13,00   MR
15/03/2017A1 LA01          200,00  LA01
20/08/2017A1RC            14,00   RC
11/05/2017A2MP            25,00   MP
11/04/2017A2EC            32,00   EC
10/02/2017A3AM            44,00   AM
04/01/2017A3 LA01          360,00  LA01
31/03/2017A1 LA02          175,00  LA02
20/06/2017A2JS            27,00   JS

 

And the Areas table:

 

CODAREAAREA
A1Area1
A2Area2
A3Area3

 

Thank you.

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

Hello, I guess CONCEPTE FAC is a calculated column.

 

If it is any if statement maybe try

CONCATENATE([COL],[CONCEPTE]) instead.

 

Best regards

  • Raul's avatar
    Raul
    Post Patron
    8 years ago

    No, that is not the problem. I have changed the null values by 0 and the result is still incorrect:

     

    Please, help me!!!!!

  • Pavlous's avatar
    Pavlous
    Advocate II
    8 years ago

    Yes, in this case it could be the issue in Axis value. Try to make Table only with Raw attributes and the measure during the date. It should give you the result. The formulas are perfectly OK.

  • Raul's avatar
    Raul
    Post Patron
    8 years ago

    Hi, Pavlous and Floriankx and thank you very much for your comments.

    Really, Concepte FAC it's not a calculated column, it's a field in the table. The Table1 is the result to Append two tables (TableA and TableB) with this fields:

    TableA (Date, CodArea, Col, Cost)

    TableB (Date, CodArea, Col, Concepte, Fact, ConcepteFac) where Concepte Fac it's a calculated column: IF(Concepte<>"", Concepte, Col)

     

    The result Table1 it's (Date, CodArea, Col, Concepte, Cost, Fact, Concepte Fac). I try to change the calculated  column by the concatenate formula and I explain how it has gone.

    Thanks.

  • Raul's avatar
    Raul
    Post Patron
    8 years ago

    Hi Anonymous

    Your exemple it's awesome :smileysurprised:, thanks a lot. This is exactly what I want and the two matrix tables present the correct results. 

     

    But, I changed the conditional column by the calculated column concatenating the two fields as Floriankx commented and the result, in my case, is still incorrect. It's the same as I had with the conditional column and I can not show the totals by Area in the whole column.

     

    What's happens? :smileysad:

     

  • Raul's avatar
    Raul
    Post Patron
    8 years ago

    A difference between your Table1 or Merge1 origin table and my table are that the values of the rows for the Cost and Fact columns are null intead of blank that your exemple.

     

    Merge1

    My table.

     

    Could this be the problem? Thank you.

  • Raul's avatar
    Raul
    Post Patron
    8 years ago

    I've found the solution!!!

    The formula for the measure is:

    TempCOST = CALCULATE(SUM(Table1[Cost]);ALLSELECTED(Table1);VALUES(Table1[Area]))

     

    Thank you Anonymous, Pavlous and Floriankx for your help.

    Bye!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Raul,

     

    Can you help me, how to display same row number in total based on category. that's my current problem.