Custom filter that applied for same fact table

[eddiechang]

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.

[MFelix]

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]

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]

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.

[eddiechang]

Hi, Any expert or contributer can help.

Looking forward a helping hand.

Thanks in advance.

[v-jiascu-msft]

Hi @eddiechang,

Please try out the method below.

1. Add a calculated column to identify if one product has previous purchase.

IfPurchaseFromSup1 =
VAR firstSaleDate =
    CALCULATE (
        MIN ( 'Table1'[TransactionDate] ),
        ALLEXCEPT ( Table1, Table1[ProductCode] ),
        'Table1'[TransactionType] = "sales"
    )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table1' ),
            ALLEXCEPT ( Table1, Table1[ProductCode] ),
            'Table1'[TransactionType] = "purchase",
            'Table1'[CustomerorSupplierCode] = "sup1"
        )
            > 0,
        "Y",
        "N"
    )

2. Create a new measure like this.

Sales 2 = 
CALCULATE (
    SUM ( Table1[Amount] ),
    Table1[TransactionType] = "sales",
    Table1[IfPurchaseFromSup1] = "Y",
    ALL ( Table1[CustomerorSupplierCode] )
)

Best Regards,

Dale

[eddiechang]

Hi @v-jiascu-msft,

Thanks for your reply and appricieted!

However, the requirement that i wish is the CustomerSupplierCode selection is dynamic by slicer selection. (single selection will do)

Due to the user would select other supplier too.

Thanks again.

[v-jiascu-msft]

Hi @eddiechang,

The users can select other suppliers. Please try out it in this file.

Best Regards,

Dale

[eddiechang]

Hi @v-jiascu-msft,

Thanks again for the prompt feedback and help. However, the case not work when for datamodel that not in single table.

Enclosed with my sample in pbix.

Hope can clear the doubt.

Thanks.

my pbix

[v-jiascu-msft]

Hi @eddiechang,

A few changes are applied in the calculated column and the measure. The file is here.

IfPurchaseFromSup1 = 
VAR firstSaleDate =
    CALCULATE (
        MIN ( Fact_table[TransactionDate] ),
        ALLEXCEPT ( Fact_table, Fact_table[ProductNo] ),
        Fact_table[TransactionType] = "sales"
    )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( Fact_table ),
            ALLEXCEPT ( Fact_table, Fact_table[ProductNo] ),
            Fact_table[TransactionType] = "purchase",
            Fact_table[CustomerorSupplierCode] = "AP-1",
            Fact_table[TransactionDate]<=firstSaleDate
        )
            > 0,
        "Y",
        "N"
    )

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

Best Regards,

Dale

[eddiechang]

Hi @v-jiascu-msft,

Wish you a happy new year 2018 and thanks again for the reply.

I think i understand your script but i realized that it only applied for sup1 in my case but it not dynamically solve for other sup selection.

I attached a sample with addon sup4. It will not reflect the purchase and sales that only for sup4.

Problem.pbix