PowerPivot - Granularity issues with a many-to-many relationship

[Gabe B]

I have two fact tables, one that is a list of stays at multiple hotels with 4 columns (Location, arrival date, departure date, member ID), another that's a series of transactions with a bunch of details (location, transaction date, member id, item sold, item category, etc). Both tables have the same location, member id, and dates used multiple times, the transaction table has the same item and item category multiple times. I have the appropriate dimension tables for members, locations, items and categories, and a calendar table.

My objective is to evaluate the amount of sales per night stayed at each hotel. I have written the following calculated column using DAX:

=
CALCULATE (
    SUM ( FactClubChits[Net Amount] ),
    DATESBETWEEN (
        'FactClubChits'[Date of Record],
        'StayWindows'[arrDateDataTextBox],
        'StayWindows'[depDateDataTextBox]),
    FILTER (
        'DimMembers',
        'DimMembers'[ClubMemberCode] = 'StayWindows'[MemberCode]
    ),
    FILTER (
        'DimCompanies',
        'DimCompanies'[CompanyName] = 'StayWindows'[HotelName]
    )
)

This gives accurate results by location, and Member ID but when I create a pivot table for item category I get repeating values for all rows. Is there a way I can alter my dax to promote the granularity that's desired? Thanks in advance for any help and advice someone can provide.

 

[Gabe B]

Bump