Forum Discussion

Credo's avatar
Credo
Icon for Helper I rankHelper I
8 years ago

Total by level with bridge table

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?

 

Need Help

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Credo,

     

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

     

    Regards,

    Yuliana Gu

    • Credo's avatar
      Credo
      Icon for Helper I rankHelper I
      8 years ago

      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.