cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ritesh_Air
Post Patron
Post Patron

Filter based on subset of data

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

 

 

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-yingjl
Community Support
Community Support

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.

View solution in original post

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

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors