IF CONTAINS EXCLOUDE BUT INCLUDE IN ALL OTHERS

[Aydeedglz]

Hi I am creating a table with top 10 costumers and the rest grouped in All Others category, that is not the issue, I have alreayd made it, the thing is, in the top 10 I don't want to see 3 costumers but I do want to see them in the All Others category with the rest of the costumers that were not in the top 10, this costumers are in the top 10, becuase if it's value $$, I want that to be ignores for those 3 and then be added in the All Others category

This 3 costumers have something in common they have "NONGM" between the name I have traid with CONTAINSSTRING but that return 1,0 si not allowing me, here are the formulas that I have:

d_TopTradingChildOther =

//If a category is ranked within the Top N, give us the value for that category.

if([d_TopPartnerRank] <= [d_TopX],

[d_TotalPD],  

//else we're outside the Top N, and we're looking at the "Other" category, aggregate the value for all categories outside the TopN

if(SELECTEDVALUE(ChildAccountOthers[Child]) = "All Other", sumx(filter(all(ChildAccountOthers[Child]), [d_TopPartnerRank] > [d_TopX]),  [d_TotalPD])

    ))

d_TopPartnerRank = IF(SELECTEDVALUE(ChildAccountOthers[Child]) = "All Other" , [d_TopX] + 1, RANKX(ALL(ChildAccountOthers), [d_TotalPD] )) //If we are looking at the "Other" category, then set the rank to be 1 higher than our TopX meausre, else give us the rank of the category by trading value

d_TopX = 10

How can I create a table where the top 10 will we shown, exclouding "costumer 1 NONGM", "customer 2 NONGM", "CUSTOMER 3 nongm" BUT I do want to see them in the group "All Others"?? (This is why I can't filter the table, becuase they won't be seen in the All Others"

[v-linyulu-msft]

Thanks for the reply from DataNinja777 , please allow me to provide another insight:

Hi, @Aydeedglz 
Thanks for reaching out to the Microsoft fabric community forum.

You can try the following example to achieve your needs:

Replace the measures in the steps with the measures you need, and replace the contents of variable b with the customers you don't want, such as "Customer 1 NONGM," "Customer 2 NONGM," "Customer 3 NONGM." Keep the other steps unchanged to get the desired result.

MEASURE = 
VAR maxy =
    YEAR ( CALCULATE ( MAX ( 'Table'[DATE] ), ALLSELECTED ( 'Table' ) ) )
VAR a =
    SUMMARIZE (
        TOPN (
            [Parameter Value],
            SUMMARIZE (
                ALLSELECTED ( 'Table' ),
                [state],
                "Sum", CALCULATE ( SUM ( 'Table'[VALUES] ), YEAR ( 'Table'[DATE] ) = maxy  )
            ),
            [Sum], DESC
        ),
        [state]
    )
VAR b={"AL","IL"}
RETURN
    IF (
        SELECTEDVALUE ( 'STATE2'[state] ) = "All Other",
        CALCULATE ( SUM ( 'Table'[VALUES] ), NOT ( 'Table'[state] IN a )||'Table'[state] IN b ),
        CALCULATE (
            SUM ( 'Table'[VALUES] ),
            'Table'[state] IN a,
            NOT ( 'Table'[state] IN b ),
            'Table'[state] IN VALUES ( 'STATE2'[state] )
        )
    )

Here is the detailed link:

Solved: Struggling with elements required for 'TOP N w/ AL... - Microsoft Fabric Community

I hope these steps help you solve the problem. If you have any questions or need further assistance, please feel free to let me know!

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[DataNinja777]

Hi @Aydeedglz ,

To achieve your desired outcome, where the Top 10 customers exclude those with "NONGM" in their names but include them in the "All Others" category, you can modify your DAX formulas accordingly. The d_TopTradingChildOther formula should first ensure that "NONGM" customers are not considered as part of the Top 10. This can be done by adding a CONTAINSSTRING check to exclude those customers from being ranked within the top categories. The updated formula for d_TopTradingChildOther would look like this:

d_TopTradingChildOther =
IF (
    [d_TopPartnerRank] <= [d_TopX] 
        && NOT CONTAINSSTRING(SELECTEDVALUE(ChildAccountOthers[Child]), "NONGM"),
    [d_TotalPD],
    IF (
        SELECTEDVALUE(ChildAccountOthers[Child]) = "All Other",
        SUMX (
            FILTER (
                ALL (ChildAccountOthers[Child]),
                [d_TopPartnerRank] > [d_TopX] 
                || CONTAINSSTRING(ChildAccountOthers[Child], "NONGM")
            ),
            [d_TotalPD]
        )
    )
)

Additionally, the d_TopPartnerRank formula should be adjusted to exclude customers with "NONGM" in their names from the ranking process. This ensures that such customers do not interfere with the Top 10 logic but can still be considered part of the "All Others" aggregation. The revised d_TopPartnerRank formula is as follows:

d_TopPartnerRank =
IF (
    SELECTEDVALUE(ChildAccountOthers[Child]) = "All Other",
    [d_TopX] + 1,
    RANKX (
        FILTER (
            ALL (ChildAccountOthers),
            NOT CONTAINSSTRING(ChildAccountOthers[Child], "NONGM")
        ),
        [d_TotalPD],
        ,
        DESC
    )
)

These adjustments ensure that customers with "NONGM" in their names are excluded from the Top 10 display but are aggregated in the "All Others" category along with other customers outside the Top 10. This approach maintains the integrity of the Top 10 while grouping the specific customers you want to exclude into the "All Others" category. Let me know if you need further clarification or additional modifications.

Best regards,

[Aydeedglz]

Hi Thanks for the approach! the formula works, but when using in visuals all of the costuemrs appear with blank and if I try to hide blanks amount gets affected, so my visuals have the "all other" costumers showing blank and I can't take them off or the value gets affected