cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jmenas Member
Member

DAX - Exclude Categories and add to Values to other Categories

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?

 

2019-01-31 17_11_06-Sales.csv - Excel.png

 

Cheers,

J. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX - Exclude Categories and add to Values to other Categories

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.

 

Capture.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: DAX - Exclude Categories and add to Values to other Categories

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.

 

Capture.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jmenas Member
Member

Re: DAX - Exclude Categories and add to Values to other Categories

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.