Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have always had a hard time using non-distinct columns in filtered measures.
I need to calculate revenue where internal and external Sales Rep IDs are not the same.
I can do that using the SalesRepKEYs in the Sales table but the Keys for Internal and External reps are not the same for the Reps.
I need to use the RepIDs (where InternalRepID <> ExternalRepID )
The Tables are linked via the Internal and External RepKEY.
Sales Table | InternalRep Table | |||
Revenue | Number | InternalRepKey | Distinct String | |
InvoiceID | String | InternalRepID | String | |
InternalRepKEY | String | |||
ExternalRepKEY | String | |||
ExternalRep Table | ||||
InternalRepKEY <> External RepKEY | ExternalRepKey | Distinct String | ||
ExternalRepID | String | |||
Need to bring in InvoiceID and Revenue where InternalRepID <> ExternalRepID |
SAMPLE DATA | |||||||||
Sales Table | InternalRep Table | ExternalRep Table | |||||||
Revenue | InvoiceID | InternalRepKEY | ExternalRepKEY | InternalRepKey | InternalRepID | EnternalRepKey | EnternalRepID | ||
1000 | 1 | 30 | 12 | 30 | MJ | 12 | SR | ||
700 | 2 | 25 | 37 | 25 | RT | 37 | RT | ||
200 | 3 | 15 | 43 | 15 | DD | 43 | DD | ||
600 | 4 | 7 | 9 | 7 | SR | 9 | LY | ||
Expected Results | |||||||||
Sales where Internal Rep ID <> External Rep ID | |||||||||
InvoiceID | InternalRepID | EnternalRepID | Revenue | ||||||
1 | MJ | SR | 1000 | ||||||
4 | SR | LY | 600 |
Hi @Anonymous
I make a test with the following tables, if my data example is not as yours, please let me know.
Create measures in "Sales Table" to get the "InternalRepID" and "ExternalRepID" assicated with the "InternalRepKey" and "ExternalRepKey" in "Sales Table".
lookup_internalid = LOOKUPVALUE('InternalRep Table'[InternalRepID],'InternalRep Table'[ InternalRepKey],MAX('Sales Table'[InternalRepKEY])) lookup_externalid = LOOKUPVALUE('ExternalRep Table'[ExternalRepID],'ExternalRep Table'[ExternalRepKey],MAX('Sales Table'[ExternalRepKEY]))
then create a measure to sum the "Revenue" where InternalRepID <> ExternalRepID,
sum1 calculate the sum for each row and let the cells as spacewhere InternalRepID = ExternalRepID,
sum2 calculate the sum for all rows and let the cells as spacewhere InternalRepID = ExternalRepID, when you add it in the table visual, it will show many rows than expected, you could set "sum1" in Visual level filter" as "show items when value is not blank".
sum1 = CALCULATE(SUM('Sales Table'[Revenue]),FILTER('Sales Table',[lookup_internalid]<>[lookup_externalid])) sum2 = CALCULATE(SUM('Sales Table'[Revenue]),FILTER('Sales Table',[lookup_internalid]<>[lookup_externalid]))
Best Regards
Maggie
Hi,
Share some data and show the expected result.
I posted sample data and expected results. It did not appear to link to your message.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |