Forum Discussion
Solved
Measure Help Across Tables With Bridge
Hello - I need some assistance with calculating a measure. Here is what I have: Table A: Table B: These two tables are joined through a bridge table by Account Numb...
Create relationships between Table A and Table B by Bridge Table
Calculate sum of USOC Charges where AccountID = AccountID and EOM Load Date = EOM Load Date
Measure = CALCULATE ( SUM ( Sheet2[ USOC_Charge ] ), FILTER ( ALL ( Sheet2 ), [AccountID] = SELECTEDVALUE ( Sheet2[AccountID] ) && [EOM Load Date] = SELECTEDVALUE ( Sheet2[EOM Load Date] ) ) )How do you want to use Period (in Table A) in visualization?
How does Period (in Table A) affect sum of USOC Charges? Could you make me clear about these?
Best Regards
Maggie
Hi MFelix
Attached are three Excel snapshots of the table structure.
What I am looking to do is create visualizations using Period (in Table A) where USOC Charges (In Table B) are summed where AccountID = AccountID and EOM Load Date = EOM Load Date. That will tie charges to an account number at a specified period.
Bridge Table:
| AccountID |
| 12345 |
| 56789 |
| 98765 |
Table A
| AccountID | EOM Load Date | Period |
| 12345 | 3/1/18 12:00 AM | -1 |
| 12345 | 4/1/18 12:00 AM | 0 |
| 12345 | 5/1/18 12:00 AM | 1 |
| 12345 | 6/1/18 12:00 AM | 2 |
| 12345 | 7/1/18 12:00 AM | 3 |
| 12345 | 8/1/18 12:00 AM | 4 |
| 12345 | 9/1/18 12:00 AM | 5 |
| 12345 | 10/1/18 12:00 AM | 6 |
| 12345 | 11/1/18 12:00 AM | 7 |
| 12345 | 12/1/18 12:00 AM | 8 |
| 12345 | 1/1/19 12:00 AM | 9 |
| 12345 | 2/1/19 12:00 AM | 10 |
| 12345 | 3/1/19 12:00 AM | 11 |
| 12345 | 4/1/19 12:00 AM | 12 |
| 56789 | 3/1/18 12:00 AM | -1 |
| 56789 | 4/1/18 12:00 AM | 0 |
| 56789 | 5/1/18 12:00 AM | 1 |
| 56789 | 6/1/18 12:00 AM | 2 |
| 56789 | 7/1/18 12:00 AM | 3 |
| 56789 | 8/1/18 12:00 AM | 4 |
| 56789 | 9/1/18 12:00 AM | 5 |
| 56789 | 10/1/18 12:00 AM | 6 |
| 56789 | 11/1/18 12:00 AM | 7 |
| 56789 | 12/1/18 12:00 AM | 8 |
| 56789 | 1/1/19 12:00 AM | 9 |
| 56789 | 2/1/19 12:00 AM | 10 |
| 56789 | 3/1/19 12:00 AM | 11 |
| 56789 | 4/1/19 12:00 AM | 12 |
| 98765 | 1/1/18 12:00 AM | -1 |
| 98765 | 2/1/18 12:00 AM | 0 |
| 98765 | 3/1/18 12:00 AM | 1 |
| 98765 | 4/1/18 12:00 AM | 2 |
| 98765 | 5/1/18 12:00 AM | 3 |
| 98765 | 6/1/18 12:00 AM | 4 |
| 98765 | 7/1/18 12:00 AM | 5 |
| 98765 | 8/1/18 12:00 AM | 6 |
| 98765 | 9/1/18 12:00 AM | 7 |
| 98765 | 10/1/18 12:00 AM | 8 |
| 98765 | 11/1/18 12:00 AM | 9 |
| 98765 | 12/1/18 12:00 AM | 10 |
| 98765 | 1/1/19 12:00 AM | 11 |
| 98765 | 2/1/19 12:00 AM | 12 |
Table B
| AccountID | EOM Load Date | USOC_Charge |
| 12345 | 3/1/18 12:00 AM | $ 77.55 |
| 12345 | 4/1/18 12:00 AM | $ 22.62 |
| 12345 | 5/1/18 12:00 AM | $ 69.33 |
| 12345 | 6/1/18 12:00 AM | $ 78.06 |
| 12345 | 7/1/18 12:00 AM | $ 0.70 |
| 12345 | 8/1/18 12:00 AM | $ 118.66 |
| 12345 | 9/1/18 12:00 AM | $ 94.46 |
| 12345 | 10/1/18 12:00 AM | $ 92.28 |
| 12345 | 11/1/18 12:00 AM | $ 76.57 |
| 12345 | 12/1/18 12:00 AM | $ 60.30 |
| 12345 | 1/1/19 12:00 AM | $ 64.74 |
| 12345 | 2/1/19 12:00 AM | $ 29.28 |
| 12345 | 3/1/19 12:00 AM | $ 33.94 |
| 12345 | 4/1/19 12:00 AM | $ 117.38 |
| 56789 | 3/1/18 12:00 AM | $ 55.13 |
| 56789 | 4/1/18 12:00 AM | $ 26.41 |
| 56789 | 5/1/18 12:00 AM | $ 109.21 |
| 56789 | 6/1/18 12:00 AM | $ 9.35 |
| 56789 | 7/1/18 12:00 AM | $ 27.80 |
| 56789 | 8/1/18 12:00 AM | $ 106.71 |
| 56789 | 9/1/18 12:00 AM | $ 32.18 |
| 56789 | 10/1/18 12:00 AM | $ 72.95 |
| 56789 | 11/1/18 12:00 AM | $ 100.94 |
| 56789 | 12/1/18 12:00 AM | $ 35.34 |
| 56789 | 1/1/19 12:00 AM | $ 86.40 |
| 56789 | 2/1/19 12:00 AM | $ 95.15 |
| 56789 | 3/1/19 12:00 AM | $ 57.72 |
| 56789 | 4/1/19 12:00 AM | $ 19.50 |
| 98765 | 1/1/18 12:00 AM | $ 56.95 |
| 98765 | 2/1/18 12:00 AM | $ 1.41 |
| 98765 | 3/1/18 12:00 AM | $ 104.44 |
| 98765 | 4/1/18 12:00 AM | $ 92.68 |
| 98765 | 5/1/18 12:00 AM | $ 44.42 |
| 98765 | 6/1/18 12:00 AM | $ 113.94 |
| 98765 | 7/1/18 12:00 AM | $ 24.90 |
| 98765 | 8/1/18 12:00 AM | $ 55.38 |
| 98765 | 9/1/18 12:00 AM | $ 76.33 |
| 98765 | 10/1/18 12:00 AM | $ 47.40 |
| 98765 | 11/1/18 12:00 AM | $ 3.56 |
| 98765 | 12/1/18 12:00 AM | $ 68.52 |
| 98765 | 1/1/19 12:00 AM | $ 71.79 |
| 98765 | 2/1/19 12:00 AM | $ 77.53 |
| 12345 | 3/1/18 12:00 AM | $ 60.96 |
| 12345 | 4/1/18 12:00 AM | $ 103.70 |
| 12345 | 5/1/18 12:00 AM | $ 76.17 |
| 12345 | 6/1/18 12:00 AM | $ 60.20 |
| 12345 | 7/1/18 12:00 AM | $ 50.82 |
| 12345 | 8/1/18 12:00 AM | $ 99.11 |
| 12345 | 9/1/18 12:00 AM | $ 94.23 |
| 12345 | 10/1/18 12:00 AM | $ 23.97 |
| 12345 | 11/1/18 12:00 AM | $ 60.65 |
| 12345 | 12/1/18 12:00 AM | $ 66.46 |
| 12345 | 1/1/19 12:00 AM | $ 113.50 |
| 12345 | 2/1/19 12:00 AM | $ 117.31 |
| 12345 | 3/1/19 12:00 AM | $ 70.51 |
| 12345 | 4/1/19 12:00 AM | $ 69.50 |
| 56789 | 3/1/18 12:00 AM | $ 101.76 |
| 56789 | 4/1/18 12:00 AM | $ 47.67 |
| 56789 | 5/1/18 12:00 AM | $ 21.09 |
| 56789 | 6/1/18 12:00 AM | $ 43.91 |
| 56789 | 7/1/18 12:00 AM | $ 34.22 |
| 56789 | 8/1/18 12:00 AM | $ 114.06 |
| 56789 | 9/1/18 12:00 AM | $ 91.24 |
| 56789 | 10/1/18 12:00 AM | $ 70.55 |
| 56789 | 11/1/18 12:00 AM | $ 37.39 |
| 56789 | 12/1/18 12:00 AM | $ 45.25 |
| 56789 | 1/1/19 12:00 AM | $ 97.57 |
| 56789 | 2/1/19 12:00 AM | $ 19.61 |
| 56789 | 3/1/19 12:00 AM | $ 58.42 |
| 56789 | 4/1/19 12:00 AM | $ 106.59 |
| 98765 | 1/1/18 12:00 AM | $ 97.24 |
| 98765 | 2/1/18 12:00 AM | $ 25.53 |
| 98765 | 3/1/18 12:00 AM | $ 65.36 |
| 98765 | 4/1/18 12:00 AM | $ 112.28 |
| 98765 | 5/1/18 12:00 AM | $ 7.49 |
| 98765 | 6/1/18 12:00 AM | $ 55.21 |
| 98765 | 7/1/18 12:00 AM | $ 96.36 |
| 98765 | 8/1/18 12:00 AM | $ 20.20 |
| 98765 | 9/1/18 12:00 AM | $ 11.71 |
| 98765 | 10/1/18 12:00 AM | $ 54.52 |
| 98765 | 11/1/18 12:00 AM | $ 55.53 |
| 98765 | 12/1/18 12:00 AM | $ 14.94 |
| 98765 | 1/1/19 12:00 AM | $ 6.33 |
| 98765 | 2/1/19 12:00 AM | $ 37.64 |
| 12345 | 3/1/18 12:00 AM | $ 89.76 |
| 12345 | 4/1/18 12:00 AM | $ 69.25 |
| 12345 | 5/1/18 12:00 AM | $ 63.32 |
| 12345 | 6/1/18 12:00 AM | $ 65.18 |
| 12345 | 7/1/18 12:00 AM | $ 65.86 |
| 12345 | 8/1/18 12:00 AM | $ 106.23 |
| 12345 | 9/1/18 12:00 AM | $ 51.29 |
| 12345 | 10/1/18 12:00 AM | $ 65.64 |
| 12345 | 11/1/18 12:00 AM | $ 102.04 |
| 12345 | 12/1/18 12:00 AM | $ 68.47 |
| 12345 | 1/1/19 12:00 AM | $ 47.55 |
| 12345 | 2/1/19 12:00 AM | $ 99.94 |
| 12345 | 3/1/19 12:00 AM | $ 75.78 |
| 12345 | 4/1/19 12:00 AM | $ 41.39 |
| 56789 | 3/1/18 12:00 AM | $ 5.50 |
| 56789 | 4/1/18 12:00 AM | $ 56.80 |
| 56789 | 5/1/18 12:00 AM | $ 112.26 |
| 56789 | 6/1/18 12:00 AM | $ 32.94 |
| 56789 | 7/1/18 12:00 AM | $ 22.11 |
| 56789 | 8/1/18 12:00 AM | $ 34.50 |
| 56789 | 9/1/18 12:00 AM | $ 60.07 |
| 56789 | 10/1/18 12:00 AM | $ 48.60 |
| 56789 | 11/1/18 12:00 AM | $ 117.86 |
| 56789 | 12/1/18 12:00 AM | $ 1.63 |
| 56789 | 1/1/19 12:00 AM | $ 7.50 |
| 56789 | 2/1/19 12:00 AM | $ 24.54 |
| 56789 | 3/1/19 12:00 AM | $ 48.61 |
| 56789 | 4/1/19 12:00 AM | $ 119.79 |
| 98765 | 1/1/18 12:00 AM | $ 81.06 |
| 98765 | 2/1/18 12:00 AM | $ 21.01 |
| 98765 | 3/1/18 12:00 AM | $ 4.22 |
| 98765 | 4/1/18 12:00 AM | $ 18.24 |
| 98765 | 5/1/18 12:00 AM | $ 97.74 |
| 98765 | 6/1/18 12:00 AM | $ 35.11 |
| 98765 | 7/1/18 12:00 AM | $ 65.60 |
| 98765 | 8/1/18 12:00 AM | $ 83.68 |
| 98765 | 9/1/18 12:00 AM | $ 83.75 |
| 98765 | 10/1/18 12:00 AM | $ 104.29 |
| 98765 | 11/1/18 12:00 AM | $ 62.05 |
| 98765 | 12/1/18 12:00 AM | $ 67.64 |
| 98765 | 1/1/19 12:00 AM | $ 87.09 |
| 98765 | 2/1/19 12:00 AM | $ 59.23 |
| 12345 | 3/1/18 12:00 AM | $ 32.63 |
| 12345 | 4/1/18 12:00 AM | $ 3.61 |
| 12345 | 5/1/18 12:00 AM | $ 16.01 |
| 12345 | 6/1/18 12:00 AM | $ 38.17 |
| 12345 | 7/1/18 12:00 AM | $ 16.29 |
| 12345 | 8/1/18 12:00 AM | $ 76.40 |
| 12345 | 9/1/18 12:00 AM | $ 77.10 |
| 12345 | 10/1/18 12:00 AM | $ 86.67 |
| 12345 | 11/1/18 12:00 AM | $ 78.53 |
| 12345 | 12/1/18 12:00 AM | $ 29.82 |
| 12345 | 1/1/19 12:00 AM | $ 64.31 |
| 12345 | 2/1/19 12:00 AM | $ 108.67 |
| 12345 | 3/1/19 12:00 AM | $ 104.45 |
| 12345 | 4/1/19 12:00 AM | $ 64.22 |
| 56789 | 3/1/18 12:00 AM | $ 30.77 |
| 56789 | 4/1/18 12:00 AM | $ 105.79 |
| 56789 | 5/1/18 12:00 AM | $ 110.69 |
| 56789 | 6/1/18 12:00 AM | $ 29.00 |
| 56789 | 7/1/18 12:00 AM | $ 9.79 |
| 56789 | 8/1/18 12:00 AM | $ 28.39 |
| 56789 | 9/1/18 12:00 AM | $ 59.57 |
| 56789 | 10/1/18 12:00 AM | $ 48.82 |
| 56789 | 11/1/18 12:00 AM | $ 9.41 |
| 56789 | 12/1/18 12:00 AM | $ 70.07 |
| 56789 | 1/1/19 12:00 AM | $ 20.06 |
| 56789 | 2/1/19 12:00 AM | $ 99.93 |
| 56789 | 3/1/19 12:00 AM | $ 57.90 |
| 56789 | 4/1/19 12:00 AM | $ 64.26 |
| 98765 | 1/1/18 12:00 AM | $ 67.86 |
| 98765 | 2/1/18 12:00 AM | $ 53.07 |
| 98765 | 3/1/18 12:00 AM | $ 39.21 |
| 98765 | 4/1/18 12:00 AM | $ 84.36 |
| 98765 | 5/1/18 12:00 AM | $ 6.87 |
| 98765 | 6/1/18 12:00 AM | $ 44.89 |
| 98765 | 7/1/18 12:00 AM | $ 101.80 |
| 98765 | 8/1/18 12:00 AM | $ 41.34 |
| 98765 | 9/1/18 12:00 AM | $ 31.54 |
| 98765 | 10/1/18 12:00 AM | $ 22.20 |
| 98765 | 11/1/18 12:00 AM | $ 19.28 |
| 98765 | 12/1/18 12:00 AM | $ 48.96 |
| 98765 | 1/1/19 12:00 AM | $ 110.47 |
| 98765 | 2/1/19 12:00 AM | $ 20.66 |
I could not figure out how to attach an Excel file so if this does not help, please let me know and I will provide sample data another way.
Matt
Create relationships between Table A and Table B by Bridge Table
Calculate sum of USOC Charges where AccountID = AccountID and EOM Load Date = EOM Load Date
Measure =
CALCULATE (
SUM ( Sheet2[ USOC_Charge ] ),
FILTER (
ALL ( Sheet2 ),
[AccountID] = SELECTEDVALUE ( Sheet2[AccountID] )
&& [EOM Load Date] = SELECTEDVALUE ( Sheet2[EOM Load Date] )
)
)
How do you want to use Period (in Table A) in visualization?
How does Period (in Table A) affect sum of USOC Charges? Could you make me clear about these?
Best Regards
Maggie