Forum Discussion
Divide and Lookup
Hello Everyone,
I have 2 tables :
| ID | Location | Tracking |
| 11 | USA | 10 |
| 22 | Malaysia | 3 |
| 33 | India | 4 |
| 44 | Germany | 5 |
| 55 | India | 4 |
| 66 | Singapore | 1 |
| 77 | USA | 2 |
| 11 | India | 6 |
| 11 | China | 5 |
| 11 | Japan | 4 |
| 22 | Malaysia | 9 |
| 22 | Japan | 1 |
| ID | Final Tracking |
| 11 | 9 |
| 22 | 4 |
| 33 | 9 |
| 44 | 6 |
| 55 | 10 |
| 66 | 10 |
| 77 | 8 |
I wanted to create a measure where I need to divide Tracking by Final Tracking, but I need to add the duplicates on the first table.
Sample : ID 11 have 4 entries with a total of 25 trackings. so (25/9)-1 = 1.77
Thanks!
Hi Anonymous
1. Place Table2[ID] in a table visual
2. Create this measure and palce it in the visual
Measure = DIVIDE ( SUM ( Table2[Tracking] ), CALCULATE ( SUM ( Table1[Tracking] ), TREATAS ( DISTINCT ( Table2[ID] ), Table1[ID] ) ) ) - 1Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi Anonymous ,
You can create a measure as below:
Measure = VAR _tracking = CALCULATE ( SUM ( 'Tracking'[Tracking] ), FILTER ( 'Tracking', 'Tracking'[ID] = MAX ( 'Final Tracking'[ID] ) ) ) VAR _finaltraking = SUM ( 'Final Tracking'[Final Tracking] ) RETURN DIVIDE ( _tracking, _finaltraking, 0 ) - 1Best Regards
2 Replies
Hi Anonymous
1. Place Table2[ID] in a table visual
2. Create this measure and palce it in the visual
Measure = DIVIDE ( SUM ( Table2[Tracking] ), CALCULATE ( SUM ( Table1[Tracking] ), TREATAS ( DISTINCT ( Table2[ID] ), Table1[ID] ) ) ) - 1Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi Anonymous ,
You can create a measure as below:
Measure = VAR _tracking = CALCULATE ( SUM ( 'Tracking'[Tracking] ), FILTER ( 'Tracking', 'Tracking'[ID] = MAX ( 'Final Tracking'[ID] ) ) ) VAR _finaltraking = SUM ( 'Final Tracking'[Final Tracking] ) RETURN DIVIDE ( _tracking, _finaltraking, 0 ) - 1Best Regards