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:

 

AREAConcepte FACTCostFactTempCOSTTempFACT
Area1      1.437,25       2.880,19       1.437,25       2.880,19  
 AM           25,00        1.437,25   
 EC           20,00        1.437,25   
 JS     1.160,00        1.437,25   
 MP           35,00        1.437,25   
 MR         137,00        1.437,25   
 RC           60,00        1.437,25   
 LA01      2.778,19        2.880,19  
 LA02          102,00        2.880,19  
Area2 435,000,00435,000,00
 AM375,000,00435,000,00
 JP60,000,00435,000,00

 

where TempCOST = CALCULATE(SUM(Table1[Cost]);ALL(Table1[Concepte FAC])) and TempFACT = CALCULATE(SUM(Table1[Fact]);ALL(Table1[Concepte FAC])).

 

I need this result in the table:

AREAConcepte FACTCostFactTempCOSTTempFACT
Area1 1.437,25   2.880,19       1.437,25       2.880,19  
 AM           25,00        1.437,25        2.880,19  
 EC           20,00        1.437,25        2.880,19  
 JS     1.160,00        1.437,25        2.880,19  
 MP           35,00        1.437,25        2.880,19  
 MR         137,00        1.437,25        2.880,19  
 RC           60,00        1.437,25        2.880,19  
 LA01      2.778,19       1.437,25       2.880,19  
 LA02          102,00       1.437,25       2.880,19  
Area2 435,000,00435,000,00
 AM375,000,00435,000,00
 JP60,000,00 435,000,00

 

Which are the correct formula to the both measures?

Thank you.

 

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!!

19 Replies

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    Hello,

     

    can you show us the structure of your raw data?

    Your Measures should work perfectly.

     

    I created this RawTable:

    AreaConcepte FACTCostFact
    Area1AM           25,00   
    Area1EC           20,00   
    Area1JS     1.160,00   
    Area1MP           35,00   
    Area1MR         137,00   
    Area1RC           60,00   
    Area1LA01      2.778,19  
    Area1LA02          102,00  
    Area2AM3750
    Area2JP600

     

    I created the following Measures:

    Cost_:=SUM(Table1[Cost])

    TemCOST:=CALCULATE([Cost_];ALL(Table1[Concepte FACT]))

     

    Fact_:=SUM(Table1[Fact])

    TempFACT:=CALCULATE([Fact_];ALL(Table1[Concepte FACT]))

     

    And this is my result:

    • Raul's avatar
      Raul
      Post Patron
      8 years ago

      Hi Floriankx, thank you for your reply.

      This is the relationship between tables in the data model:

       

      Thank you!

      • Floriankx's avatar
        Floriankx
        Solution Sage
        8 years ago

        Hello,

         

        relationship doesn't seem to be the problem, as long as CodArea columns are related properly and CodArea is unique in your AREAS Table.

         

        It still should work.

         

        You can try to use CodArea of Table1 instead of Area of the AREAS Table and see if something changes.

         

        Best regards.