cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eddiechang
Helper I
Helper I

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.

 

fact.PNG

 

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.

outcome.PNG

 

Highly appreciete someone can help.

Thanks in advance.

10 REPLIES 10
MFelix
Super User III
Super User III

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.

 

sales.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

fact.PNG

Looking forward for the reply.

Thanks.

 

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.

 

datamodel.PNG

 

Dunno why the productC will appear in outcome.

 

wrong outcome.PNG

Thanks.

Hi, Any expert or contributer can help.

Looking forward a helping hand.

Thanks in advance.

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] )
)

Custom_filter_that_applied_for_same_fact_table

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

Hi @eddiechang,

 

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

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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] )
)

Custom_filter_that_applied_for_same_fact_table3

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors