Measure to Mutliple two columns from different Tables

[Mavericks334]

Hi,

I have Table A and Table B

Below are the details of the table.
[TABLE="width: 59"]
<COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY>[TR]
[TD="class: xl65, width: 78, bgcolor: transparent"]Complexity[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[/TR]
</TBODY>[/TABLE]


Table B has [TABLE="width: 107"]
<COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl65, width: 78, bgcolor: transparent"]Complexity[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Time[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]40[/TD]
[/TR]
</TBODY>[/TABLE]

There is a relationship between Table A & B.

I need to created a measure that would calculate Table A [Complexity]*Table[Time]

I am able to do this with an additional column in table A by using related, i dont wish to do that, need to create a measure so the size of the sheet would reduce.

 

[JavierGuillen]

Hi,

as a measure, you can use CALCULATE to propagate context to mimic the RELATED function:

SUMX(
VALUES( TableA[Complexity] ),
CALCULATE(
IF( ISFILTERED( TableA[Complexity] ) ,
VALUES(TableA[Complexity]) *
CALCULATE( VALUES( TableB[Time] ) , TableA )
)
)
)

 

[Mavericks334]

Hi Javier,

it measure worked however i am not getting the exact Value.

When i put this information in a pivot and compared it manually with what i have done there is a huge difference.

I have the name of the person who has completed the task in the Row and the measure in the value filed.

When i have persons name in the row field, it needs to sum all the activities together to give me a value, which is not happening.

[TABLE="width: 134"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]Name[/TD]
[TD="class: xl64, width: 115, bgcolor: white"]Complexity[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Renato[/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Javier[/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]REnato[/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Javier[/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Paul[/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Javier[/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]6[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Paul[/TD]
[TD="class: xl65, width: 115, bgcolor: transparent"]7[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]Complexity[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Time[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]1[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]10[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]2[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]15[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]3[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]20[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]4[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]25[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]5[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]30[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]6[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]35[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]7[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]40[/TD]
[/TR]
</TBODY>[/TABLE]


When i create the pivot i need to get

[TABLE="width: 162"]
<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY>[TR]
[TD="class: xl66, width: 92"]Row Labels[/TD]
[TD="class: xl66, width: 124"]Sum of Time taken[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Javier[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]75[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Paul[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]70[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Renato[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl67"]Grand Total[/TD]
[TD="class: xl66, align: right"]175[/TD]
[/TR]
</TBODY>[/TABLE]

Which is not happening in the current Measure.

 

[JavierGuillen]

You had stated before you wanted to execute the calculation as Table A [Complexity]*Table[Time].

If you only need the time from TableB to achieve the result you mention, simply modify the calculation to:

SUMX(
VALUES( TableA[Complexity] ),
CALCULATE(
IF( ISFILTERED( TableA[Complexity] ) ,
CALCULATE( VALUES( TableB[Time] ) , TableA )
)
)
)

 

[Laurent C]

As I understand it:
TableB (complexity, time) is your lookup table
TableA (name, complexity) contains your data
You have a relationship that goes from TableA to TableB
You need to get the time from table B based on the Complexity key.

You can define your measure as:
Mesure 1:=CALCULATE(
SUM('TableB'[Time])
, TableA
)

 

[Mavericks334]

Javier,

Apolgize if i had not explained it correctly earlier.

It still does not work. Below is the data, that i am using.[TABLE="width: 210"]
<COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><TBODY>[TR]
[TD="class: xl66, width: 160, bgcolor: #8db4e2"]Preparer[/TD]
[TD="class: xl67, width: 120, bgcolor: transparent"]Fund_Complexity[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Sobkowiak, Bartosz[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Sobkowiak, Bartosz[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Sobkowiak, Bartosz[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Rozmarynowska, Joanna[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Wegielska, Anna[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Wegielska, Anna[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Zalewska, Malgorzata[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Zalewska, Malgorzata[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Dobruchowska, Edyta[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Dobruchowska, Edyta[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Wlodarczak, Malgorzata[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Wlodarczak, Malgorzata[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Wlodarczak, Malgorzata[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Vupparapelli, Kiran[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Rozmarynowska, Joanna[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Sankaran, Srividya[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Tekumalla, Saritha[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Ignatowicz, Marcin[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Ignatowicz, Marcin[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Walkowiak, Magdalena[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1"]Walkowiak, Magdalena[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Genc, Anna[/TD]
[TD="class: xl69, width: 120, bgcolor: transparent, align: right"]3[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 107"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: #99ccff"]Complexity[/TD]
[TD="class: xl66, width: 79, bgcolor: #99ccff"]Times Complexity 1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #dce6f1"]1[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]1.00[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1.77[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #dce6f1"]3[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]1.79[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]4[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]2.73[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #dce6f1"]5[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]4.48[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]6[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]4.23[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #dce6f1"]7[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]6.16[/TD]
[/TR]
</TBODY>[/TABLE]


Where every u find a corresponding value in Table 1 it needs to go and multiply with that Value in Table two. If i have 3 in table 1 then it needs to go to Table two and multiple 3*1.79 to give me a corresponding value.

The result would be.
[TABLE="width: 227"]
<COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><TBODY>[TR]
[TD="class: xl66, width: 160"]Row Labels[/TD]
[TD="class: xl66, width: 142"]Sum of Desired Value[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Dobruchowska, Edyta[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]21.84[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Genc, Anna[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]5.36[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Ignatowicz, Marcin[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]10.71[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]141.79[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Sankaran, Srividya[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Sobkowiak, Bartosz[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]16.07[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Tekumalla, Saritha[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Vupparapelli, Kiran[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Walkowiak, Magdalena[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]44.81[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Wegielska, Anna[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]10.71[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Wlodarczak, Malgorzata[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]32.77[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Zalewska, Malgorzata[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67"]Grand Total[/TD]
[TD="class: xl68, align: right"]284.06[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Laurent C]

Why would you multiply a key by a factor? In that case, you had better replace the column [Times Complexity 1] by the value you wish to obtain.

 

[Mavericks334]

Your Correct i made a mistake while updating the values. it needs to lookup the related value and sum it when i create a pivot. Below is the how the result needs to reflect.

[TABLE="width: 183"]
<COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY>[TR]
[TD="class: xl63, width: 160"]Row Labels[/TD]
[TD="class: xl63, width: 84"]Sum of Time[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Dobruchowska, Edyta[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5.461076869[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Genc, Anna[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1.785441258[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Ignatowicz, Marcin[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3.570882516[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Rozmarynowska, Joanna[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24.85663969[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Sobkowiak, Bartosz[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5.356323774[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Walkowiak, Magdalena[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8.96177225[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Wegielska, Anna[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3.570882516[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Wlodarczak, Malgorzata[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8.191615304[/TD]
[/TR]
[TR]
[TD="class: xl64"]Grand Total[/TD]
[TD="class: xl63, align: right"]61.75463418[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Laurent C]

I cannot see how these results relate to the data you provided before.

Judging by the number of digits in these figures, some other - missing - data participate in this calculation.

 

[Mavericks334]

Each task is assigned a complexity Number from 1 to 7. what we are doing over here is we are normalizing everything to a complexity 1. If Complexity 1 Takes 10 Minutes and Complexity 3 Takes 30 Mintues. We are saying We could do 3 complexity 1 tasks in that time frame. So at the end of the day, we want to see how many normalized complexity 1 tasks can be completed.