Total value for all rows

[Raul]

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
	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,00	0,00	435,00	0,00
	AM	375,00	0,00	435,00	0,00
	JP	60,00	0,00	435,00	0,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:

AREA	Concepte FACT	Cost	Fact	TempCOST	TempFACT
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,00	0,00	435,00	0,00
	AM	375,00	0,00	435,00	0,00
	JP	60,00	0,00	435,00	0,00

Which are the correct formula to the both measures?

Thank you.

[Floriankx]

Hello,

can you show us the structure of your raw data?

Your Measures should work perfectly.

I created this RawTable:

Area	Concepte FACT	Cost	Fact
Area1	AM	25,00
Area1	EC	20,00
Area1	JS	1.160,00
Area1	MP	35,00
Area1	MR	137,00
Area1	RC	60,00
Area1	LA01		2.778,19
Area1	LA02		102,00
Area2	AM	375	0
Area2	JP	60	0

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]

Hi @Floriankx, thank you for your reply.

This is the relationship between tables in the data model:

Thank you!

[Floriankx]

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]

Hello,

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

Thanks.

[Floriankx]

Please give us some sample data of your Table1.

Best regards

[Raul]

Here you are:

DATE	CODAREA	COL	CONCEPTE	COST	FACT	CONCEPTE FAC
11/05/2017	A1	AM		10,00		AM
24/06/2017	A1	EC		5,00		EC
05/07/2017	A2	AM		12,00		AM
06/07/2017	A3	JS		11,00		JS
01/01/2017	A1	MP		25,00		MP
28/02/2017	A2	MR		13,00		MR
15/03/2017	A1		LA01		200,00	LA01
20/08/2017	A1	RC		14,00		RC
11/05/2017	A2	MP		25,00		MP
11/04/2017	A2	EC		32,00		EC
10/02/2017	A3	AM		44,00		AM
04/01/2017	A3		LA01		360,00	LA01
31/03/2017	A1		LA02		175,00	LA02
20/06/2017	A2	JS		27,00		JS

And the Areas table:

CODAREA	AREA
A1	Area1
A2	Area2
A3	Area3

Thank you.

[Floriankx]

Hello, I guess CONCEPTE FAC is a calculated column.

If it is any if statement maybe try

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

Best regards

[Pavlous]

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]

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.

[v-yuezhe-msft]

@Raul,

Please check if the Matrix visual in the following PBIX file  returns your expected result.

https://1drv.ms/u/s!AhsotbnGu1Nok1trPckl3J_1DQa1

Regards,
Lydia

[Raul]

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]

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

Please, help me!!!!!

[Raul]

This is the data for the table:

https://drive.google.com/open?id=1FptT-R4YmijQBR0PC4ZyDj9DbaftNVaI

Thank you very much in advance!

[Raul]

Good morning!

I've reduced tha data model to a unic table from previus Excel file and the result it's the same:

https://drive.google.com/open?id=1Q-o3SESmluP1y-piJQgUNQC_eGnRx7pI

[Raul · Accepted solution]

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

[Anonymous]

Hi Raul,

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

[Raul]

Hi @v-yuezhe-msft

Your exemple it's awesome , 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? 

[Pavlous]

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 🙂

[Raul]

Hi,

This is the result:

   

And this are the measures:

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

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

Regards.