I'm working with some sales data. We are tracking how a "scout" refers sales to "distributors". A scout can refer to any distributor. Any distributor can receive a referall from any scout. It is possible a scout makes no referrals.
I want a matrix that shows the scout at top level, and the distributor they are referring to at the next level. When I select "Show items with no data" for my scout, it also applies it to the distributor (it applies to all row fields). This means if I drill down on an individual scout, it shows every distributor in the system. It is clear to me that there is no way to apply "Show items with no data" to just the scout, but correct me if I am wrong.
Is there a workaround I might try that emulates this?
I've tried:
UnitCampaign1 =
CALCULATE(SUM(Transactions[Quantity]) + 0,
FILTER('Calendar', 'Calendar'[Accounting Date] >= EDATE(MIN('CampaignDetails 1'[Start Date]), -12)
&& 'Calendar'[Accounting Date] < EDATE(MIN('CampaignDetails 1'[Start Date]), 12)),
FILTER(Scouts, Scouts[Name] IN VALUES('Campaign 1'[Scout List]))
)
('CampaignDetails 1'[Start Date] is a single row column in a disconnected table, 'Campaign 1'[Scout List] column is a list of scouts we are tracking for this campaign. I was previously using DATESINPERIOD instead of a FILTER but it had the same result.)
but this extended my table to every date in my date table, even though the filter limits it to a period of 2 years. The date filter should be defined from the Start Date column in the table, so that future campaigns can be substituted in and the matrix will still work.
