Hi all,
I have a table that has the following columns:
- Customer
- SalesManager
- State
- Date
- ShipmentCount
- PriceCheckCount
Where the Price Check Count is simply the amount of times a customer checks for a price but don't ship the product, and the Sales Manager is the employee responsable for that customer.
So in the report I have a date range, and 3 tables. The first table is aggregating all the counts at the SalesManager level, the second one at the Customer level and the last one at the State level (just in case, these 3 tables are just visuals showing data at different levels, but the data in the model is comming from one table as I mentioned above).
What I want to show in this report is the data for all the customers that, in the selected time range, the PriceCheckCount > ShipmentCount. This condition cannot be resolved by adding a column in the table cause it needs to change dynamically based on the date range. So I created a measure called deviation to show the difference between ShipmentCount and PriceCheckCount, and I'm filtering the second table (Customer level) where that measure is negative.
Now I need to filter the other 2 tables based on the set of customers that I have as a result in the second table. I cannot use the same measure here because that will calculate the difference at the Employee and State level, and that result is not the same as filtering Employee and State counts based the set of customers where the total amount of PriceCheckCount > ShipmentCount in the selected time range.
Does anybody have an idea of how I can achieve that?
Thanks a lot.
