Forum Discussion

eddiechang's avatar
eddiechang
Helper I
8 years ago

Custom filter that applied for same fact table

Hi All,

 

I am a beginner for BI, wish to get help from expert to generate an expected outcome.

 

Generally the story is i want to analyze my supplier's purchases with combining the sales for those only product that i purchased from that particular supplier.

 

Below is my fact table.

 

 

The flow is i will use a slicer to select one of the supplier. eg. "sup1"

 

There would be a one matrix table that will show both sales and purchases value:

1. Sales - sum(amount) where TransactionType='sales', regardless of customer

2. Purchase - sum(amount) where TransactionType='purchase', only for sup1 

ps. i only want to see product that purchased from sup1.

 

Below is my expected outcome.

 

Highly appreciete someone can help.

Thanks in advance.

10 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi eddiechang,

     

    Create the following measures:

     

    Sales =
CALCULATE (
    SUM ( Fact_table[Amount] );
    Fact_table[TransactionType] = "sales";
    ALL ( Fact_table[CustomerorSupplirCode] )
)

Purchase =
CALCULATE (
    SUM ( Fact_table[Amount] );
    Fact_table[TransactionType] = "purchase"
)


Difference (Sales-Purchase) = [Sales]-[Purchase]

    Then just add your measures and the columns/rows you want and drill down to the latest level result should be as expected.

     

     

    Regards,

    MFelix

    • eddiechang's avatar
      eddiechang
      Helper I
      8 years ago

      Hi MFelix,

       

      Thanks for the reply.

       

      Very very sorry i miss out 1 row example that will reflact the problem that i faced.

       

      I did use below script to capture the sales regardless of customer. However, there one more criteria that wish to fullfill for the case above. I wish to filter out those product's sales that never purchased before from sup1 to show in the listing.

      Sales =
CALCULATE (
    SUM ( Fact_table[Amount] );
    Fact_table[TransactionType] = "sales";
    ALL ( Fact_table[CustomerorSupplirCode] )
)

       

      For below example is productC. Its has sales for outlets but never purchased from sup1. So dont wanna show in final outcome.

       

      Looking forward for the reply.

      Thanks.

       

      • eddiechang's avatar
        eddiechang
        Helper I
        8 years ago

        Add-on from my finding. Base on the sample gave to develop my data model. Still found out that those product that never purchased from this supplier would appear in listing.

         

        Purchase =
        CALCULATE (
        SUM ( Fact_table[Amount] ),
        Fact_table[TransactionType] = "purchase"
        )

         

        Sales =
        CALCULATE (
        SUM ( Fact_table[Amount] ),
        Fact_table[TransactionType] = "sales",
        ALL ( dbcr[dbcrName] )
        )

         

         

         

        Below is my data model.

         

         

        Dunno why the productC will appear in outcome.

         

        Thanks.