Columns with different filters

[Ritesh_Air]

I am reposting it because I think I made it very complicated in earlier post.

Here is the problem:

1. I am a distributor. I have products which are returned by retailers.

2. I want to calculate % of the Shipment Quantity from the retailers who had returns over total shipments for a brand. 

Sales = Table1[Quantity],Filter Table 2 = 'Sales'

Returns = Table1[Quantity],Filter Table 2 = 'Returns'

I want to calculate % 

Numerator = Table1[Quantity] > 0,Filter Table 2 = 'Returns'

Denominator = Table1[Quantity], Filter Table 2 = 'Sales'

Here is what I posted earlier.

Previous Post

@mahoneypat  : Please let me know if I explained it correctly or not? Thanks for your help so far.

Thanks,

Ritesh

[parry2k · Accepted solution]

@Ritesh_Air I looked at your original post and this one, it is a bit confusing,  the original post pbix file has return qty as a column but here you are using filters to get sales and return. You have to be very clear on how your data looks like and what output you are looking for and explain business logic. It is always good to put something in excel and explain.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[parry2k · Accepted solution]

@Ritesh_Air I looked at your original post and this one, it is a bit confusing,  the original post pbix file has return qty as a column but here you are using filters to get sales and return. You have to be very clear on how your data looks like and what output you are looking for and explain business logic. It is always good to put something in excel and explain.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Ritesh_Air]

Thanks @parry2k 

I should have done a better job creating the PBIX.  I took the output and tried to mimic the situation.

Regarding Returns and Sales:  yes, It is a filter to get both Returns and Sales. 

Sorry, about the confusion.

Thanks,

Ritesh

[parry2k]

@Ritesh_Air you should recreate the pbix file and share it. I think your problem is way smaller (as far I think) but the way you are sharing the data, making it super complex.

Just a reminder, put sample data in the format it is and explain the end result and how to get there, if you want, you can put this in excel as it is easy to write formulas in excel and from there it will be much easier. 

[Ritesh_Air]

https://drive.google.com/file/d/1ptY48wGX1URX-zl7e0iKejr9KNNazAr2/view?usp=sharing

@parry2k 

Here is the better file. Hopefully it works. I am looking to find that 42% number as explained in the PBIX.

Thanks,

Ritesh

[mahoneypat · Accepted solution]

Thanks for the new pbix.  Here is a measure that seems to work.

New Measure =
VAR __summarytable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Sales Order Detail',
            'Product'[Product Name],
            Retailer[Retailer Name]
        ),
        "@Total", CALCULATE ( SUM ( 'Sales Order Detail'[Custom Quantity] ) ),
        "@Returns", [_Return Quantity]
    )
VAR __total =
    SUMX ( __summarytable, [@Total] )
VAR __returnstotal =
    SUMX ( FILTER ( __summarytable, [@Returns] > 0 ), [@Total] )
RETURN
    DIVIDE ( __returnstotal, __total )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Ritesh_Air]

@parry2k 

@mahoneypat 

Both the solutions are elegant. Thank you so much guys. 

So If I have to make a comment in my code: 

"Create a Summary table with lowest level of granularity so it captures all the data, and then apply logic accordingly."

Correct statement?

Thanks a lot again!

Ritesh

[parry2k]

@Ritesh_Air as a blanket answer, YES

[parry2k]

@Ritesh_Air here is the measure to calculate the quantity of 720 as per your example

Qty without Return = 
CALCULATE ( 
    SUM ( 'Sales Order Detail'[Custom Quantity] ),
    FILTER ( 
        SUMMARIZE (  
            'Sales Order Detail',
            'Product'[Product ID],
            Retailer[Retailer ID],
            "__return", [_Return Quantity]
        ),
        [__return] > 0 
    )
)

here is a measure for % return

% Return = DIVIDE ( [Qty without Return], SUM ( 'Sales Order Detail'[Custom Quantity] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡