DAX - Exclude Categories and add to Values to other Categories

[Jmenas]

Hi all,

I have an issue trying to find the right set of filter for a measure. 

What I have is:

• 10 Categories.
• They all have sales by date. 

What I want is to create a measure that calculates the sales Excluding 2 of the categories (like in the screenshot). And add the half of the sales to that category to another one. 

Example: 
Category 10 = 3252 
Category 8 = 4708
Category8Groupped = 4708+(3252*0,5) = 6334

The trick here is that I don't want to see the Category 10 in my matrix or tables. Also, that can be filtered by context filters.

Does anyone have clue? which approach I can take?

Cheers,

J. 

[v-piga-msft · Accepted solution]

Hi @Jmenas,

By my tests with your data sample, you could create the calculated column below to achieve your desired output.

Column =
VAR temp =
    IF (
        'Table1'[Category] = "Category8",
        CALCULATE (
            MAX ( 'Table1'[Sales] ),
            FILTER ( 'Table1', 'Table1'[Category] = "Category10" )
        ),
        IF (
            'Table1'[Category] = "Category7",
            CALCULATE (
                MAX ( 'Table1'[Sales] ),
                FILTER ( 'Table1', 'Table1'[Category] = "Category9" )
            )
        )
    )
VAR sum_ = 'Table1'[Sales]
    + temp * 0.5
RETURN
    IF (
        OR ( 'Table1'[Category] = "Category9", 'Table1'[Category] = "Category10" ),
        BLANK (),
        sum_
    )

Here is the output.

Best Regards,

Cherry

[Jmenas]

hi @v-piga-msft,

yes, actually this helps me a lot. I used a measure to create a similar approach.

like this:

Sales =
VAR tempDP =
    IF (
        SELECTEDVALUE ( category_Table[category] ) = "category8",
        [Sales category8],
        IF (
            SELECTEDVALUE ( category_Table[category] ) = "category8",
            [Sales category8]
        )
    )
VAR sum_ =
    IF (
        OR (
            SELECTEDVALUE ( 'category_Table'[category] ) = "category7",
            SELECTEDVALUE ( 'category_Table'[category] ) = "category8"
        ),
        tempDP,
        SUM ( Overview[Sales] )
    )
RETURN
    IF (
        OR (
            SELECTEDVALUE ( 'category_Table'[category] ) = "category7+category8",
            SELECTEDVALUE ( 'category_Table'[category] ) = "category7+category8"
        ),
        BLANK (),
        sum_
    )

thanks! 

cheers,

J.