Issue with using a lookup table in calculation.

[jcloss13]

Good Morning, 

I feel like this issue should be easily solved but I can't figure it out. 

I have two tables, Table A and Table B.

Table A has the detail record and the number of transactions per location. 

Table B is a lookup table for locations, displaying the FTE Count.

I have a Many to One relationship from Table A to B on Location, like below.

Table A               

Location         Transaction Time              

A                     12

A                     9

B                     28

B                     6

Table B

Location          FTECount

A                     2

B                     3

I am having an issue using the FTE Count as a measure to calculate the time and transaction count per employee. I am able to use the Max() function to get the FTE count for each branch but that does not give me a total or translate over to a scatter plot. Ideally, the relationship would pull over the FTE Count for each locations and then the sum for the total.

In a Summary, the output I am getting looks like:

Location     Sum Trans Time      FTE Count

A                 21                           2

B                 34                           3

Total           55                            N/A

OR

Location     Sum Trans Time      sum FTE Count

A                 21                           5

B                 34                           5

Total           55                            5

I would expect to see

Location     Sum Trans Time      FTE Count

A                 21                           2

B                 34                           3

Total           55                            5

Is there something that I am missing here?

Thank you,

Jordan

[v-chuncz-msft]

@jcloss13,

Given the sample data, you just need to drag Location from Table B not Table A.

[joell001213]

사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전 #오피뷰 #아밤ψ
>조엘0013<

사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳사당휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤

사당휴게텔[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

[Ashish_Mathur · Accepted solution]

Hi,

The mistake you are committing is that you are creating a relationshop from Table A to Table B.  There are two ways to go about solving this problem:

1. Create a master table of all unique Locations from Table A and table B (let's call that new table Table C) [This master table can be create by appending Table A and Table B, removing all column expcet Location and then removing duplicated form the location column].  Then create a relationshop from Table A to Table C and Table B to Table C.  After this a simple SUM measure will work as expected.  Remember to drag location from Table C in your visual
2. If you do not wish to create Table C as proposed above, you can use the RELATEDTABLE function in Table B (after creating a relationshop from Table A to Table B).  In a calculated column in Table B, enter this formula

=SUMX(RELATEDTABLE(location_time),location_time[Transaction time])

Now create your visual from Table B.

[jcloss13]

Thank you for responding. Reading through this I realized I was making an absurd mistake that I have done correctly a hundred times. The Location Table already was the lookup and the value just had to be grabbed from here.

Thanks so much for responding!

Thank you,

Jordan

[Ashish_Mathur]

You are welcome.

[CahabaData]

to clarify where you say: the output I am getting looks like

you are getting "N/A" in what type of visual?  a table or matrix visual?     at the table level is this field modeled to be a number type or is it a text type?