Filter based on subset of data

[Ritesh_Air]

Hi,

 I am trying to modify this formula .

No. of leads with booked sales this year =
VAR _CROPYEAR =
    CALCULATE ( MAX ( GROWERORDER[CROPYEAR] ), ALL ( GROWERORDER ) )
RETURN
    CALCULATE (
        [Count of Total Optys],
        NOT ( ISBLANK ( GROWERORDER[ORDERNUMBER] ) ),
        FILTER ( GROWERORDER, GROWERORDER[CROPYEAR] = _CROPYEAR )
    )

I have a slicer of Sprint Name which has multiple products in it. But I want to calculate my sum based on only subset of those products.

For e.g.
Sprint 1 has 5 products, I want to calculate my above calculation based on just 2 products. I don't care about 3 other products.

same with sprint 2 and so on.

Sprint is a slicer in my visual. So I want to have formula something like:

No. of leads with booked sales this year = 
VAR _CROPYEAR =
    CALCULATE ( MAX ( GROWERORDER[CROPYEAR] ), ALL ( GROWERORDER ) )
RETURN
    CALCULATE (
        [Count of Total Optys],
        NOT ( ISBLANK ( GROWERORDER[ORDERNUMBER] ) ),
        FILTER ( GROWERORDER, GROWERORDER[CROPYEAR] = _CROPYEAR )
    

        /* I want to put these filters in my formula"
       1.  If sprint name is "Early Order Seed" then calculate "Count of Total Optys" where product is "Corn and Soybeans"
       2. If sprint name is "Seed Retention - Corn, Soybeans, Cotton" then calculate "Count of Total Optys" where product is "Corn, Soybeans or Cotton"
       3.  If sprint name is "Seed Retention - Sunflower, Canola" then calculate "Count of Total Optys" where product is "SUNFLOWER OIL"

       Something like this:

        IF(Opportunity[Sprint Name] = "Early Order Seed",
        WfMartsProduct, WfMartsProduct[PRODUCT_LINE_DESC] in ("CORN", "SOYBEANS")
       IF(Opportunity[Sprint Name] = "Seed Retention - Corn, Soybeans, Cotton",
        WfMartsProduct, WfMartsProduct[PRODUCT_LINE_DESC] in ("CORN", "SOYBEANS", "COTTON")
        IF(Opportunity[Sprint Name] = "Seed Retention - Sunflower, Canola",
        WfMartsProduct, WfMartsProduct[PRODUCT_LINE_DESC]="SUNFLOWER OIL")

       
       
     */
        
    
)

How do I achieve this?

Thanks in advance,

-rk

[v-yingjl · Accepted solution]

Hi @Ritesh_Air ,

Based on your description, you can modify the formula like this:

No. of leads with booked sales this year =
VAR _CROPYEAR =
    CALCULATE ( MAX ( Opportunity[Sprint Name] ), ALLSELECTED ( Opportunity ) )
RETURN
    IF (
        _CROPYEAR = "Early Order Seed",
        CALCULATE (
            [Count of Total Optys],
            FILTER (
                ALL ( WfMartsProduct ),
                WfMartsProduct[PRODUCT_LINE_DESC] IN { "CORN", "SOYBEANS" }
            )
        ),
        IF (
            _CROPYEAR = "Seed Retention - Corn, Soybeans, Cotton",
            CALCULATE (
                [Count of Total Optys],
                FILTER (
                    ALL ( WfMartsProduct ),
                    WfMartsProduct[PRODUCT_LINE_DESC] IN { "CORN", "SOYBEANS", "COTTON" }
                )
            ),
            IF (
                _CROPYEAR = "Seed Retention",
                CALCULATE (
                    [Count of Total Optys],
                    FILTER (
                        ALL ( WfMartsProduct ),
                        WfMartsProduct[PRODUCT_LINE_DESC] = "SUNFLOWER OIL"
                    )
                )
            )
        )
    )

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