cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Raul Member
Member

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Raul Member
Member

Re: Total value for all rows

I've found the solution!!!

The formula for the measure is:

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

 

Thank you @v-yuezhe-msft@Pavlous and @Floriankx for your help.

Bye!!

18 REPLIES 18
Floriankx Established Member
Established Member

Re: Total value for all rows

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:

image.png

Raul Member
Member

Re: Total value for all rows

Hi @Floriankx, thank you for your reply.

This is the relationship between tables in the data model:

 

Relationship.JPG

Thank you!

Floriankx Established Member
Established Member

Re: Total value for all rows

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.

Raul Member
Member

Re: Total value for all rows

Hello,

I tried your response but the result is the same. What I can do?

Thanks.

 

Floriankx Established Member
Established Member

Re: Total value for all rows

Please give us some sample data of your Table1.

 

Best regards

Raul Member
Member

Re: Total value for all rows

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.

Pavlous Regular Visitor
Regular Visitor

Re: Total value for all rows

Hi,

it would be heplfull if you show us what is your result at that moment. So we can think what you are doing wrong Smiley Happy

Highlighted
Raul Member
Member

Re: Total value for all rows

Hi,

This is the result:

 

Captura.JPG   Format.JPG

 

And this are the measures:

TempCOST = CALCULATE(SUM(Table1[Cost]);ALL(Table1[Concepte FAC]))

TempFACT = CALCULATE(SUM(Table1[Fact]);ALL(Table1[Concepte FAC]))

 

Regards.

Floriankx Established Member
Established Member

Re: Total value for all rows

Hello, I guess CONCEPTE FAC is a calculated column.

 

If it is any if statement maybe try

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

 

Best regards

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 165 members 1,845 guests
Please welcome our newest community members: