Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.