Total by level with bridge table

[Credo]

Hi

I am trying to calculate a Total by Customer or Date depending on matrix drillthrough

I have sales data with three levels - Month/Customer/Customer shop

I've played around with a mockup table trying to understand what different DAX functions do and I've come up with this which seems to do what I need. 

Total Sales by level =
CALCULATE (
    SUM ( 'FactSales'[Sales] ),
    ALLEXCEPT ( 'FactSales', 'FactSales'[Month], 'FactSales'[Customer])
)

The problem is once I plugged it into my actual model, it does not behave the way I tested it. 

My model consists of 4 tables. 1) FactSales 2)Calendar Dim 3) Customer bridge 4)Customer Dim

I tried using the same formula but applied to my model, I even dropped the date dimension to keep it simple for now. 

Total Sales by level =
CALCULATE (
    SUM ( 'FactSales'[Sales] ),
    ALLEXCEPT ( 'Customer Dim', 'Customer Dim'[Customer] )
)

When I try to do a matrix with Customer/Customer Shop/Sales/Sales by level measure

I get

The number is correct however I dont need it calculating for the customer shop rows where there were no sales. I've tested this a number of ways and it seems to be caused by the M - bridge (1) - M relationship that I have between my Fact and Customer Dim tables. 

How can I adjust the formula to only calculate for customer shop where there have been sales?

[v-yulgu-msft]

Hi @Credo,

Do adding field from Customer Dim tables rather than from fact table into Matrix solve this problem?

Regards,

Yuliana Gu

[Credo]

Unfortunetly no combination of fields either from the fact table or from Dim table helps fix the problem.

I was going to upload my file to provide more context, however than I noticed that there is something wrong with my Dim table.

The dim table has Customer Shop ID / Sales Channel / Customer / Customer Shop Adress. At first I noticed that the reason I have duplicate values in  Customer Shop ID is because same ID can be repeated for each Sales Channel. That does make sense. However than I noticed that a single customer shop address can multiple customer shop IDs associated with it. That makes zero sense. The ID should be 1:1 to the full physical address. 

I think that when I made my Dim table from the OLAP report, I pulled a wrong ID field. I am gonna try fix the Dim table and see whether that resolves the issue. 

[v-yulgu-msft]

Hi @Credo,

Have you resolved the problem?

Regards,

Yuliana Gu

[Greg_Deckler]

Sample data would be very helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490