Forum Discussion

willythecat's avatar
willythecat
Regular Visitor
2 years ago
Solved

Subtract multiple values from same column using different filter and rows

Dear all,   I'm pretty new to Power BI and I'm struggling to create a measure for a single table with 5 fields. The values for multiple substraction are all in the same column, but I want to use di...
DAX
multiple substraction
  • some_bih's avatar
    some_bih
    2 years ago

    Hi willythecat for test 2 measure solution there will be 5 measures as following (only color for 3 is different)

    Kudos appreciated / accept solution.

     

    Sum of MyValue = SUM(Sheet1[MyValue])

     

     
    Sum of MyValue_red =
    CALCULATE(
        SUM(Sheet1[MyValue]),
        Sheet1[Category]="Red"
    )
    Sum of MyValue_green =
    CALCULATE(
        SUM(Sheet1[MyValue]),
        Sheet1[Category]="Green"
    )
    Sum of MyValue_blue =
    CALCULATE(
        SUM(Sheet1[MyValue]),
        Sheet1[Category]="Blue"
    )
     
     

     

    Test Measure v2 =
    VAR __selected_category=SELECTEDVALUE(Sheet1[Category])
    VAR __Result=SWITCH(
        TRUE(),__selected_category="Wocolor",[Sum of MyValue]-[Sum of MyValue_blue]-[Sum of MyValue_red]-[Sum of MyValue_green],0)
    RETURN __Result
     

     

     

some_bih's avatar
some_bih
Community Champion
2 years ago

Hi willythecat one possible solution is measure as below. Adapt your table names and columns or Category values name as needed. I got amounts as yours

Kudos appreciated / accept solution.

 

Test Measure =
VAR __filtered_category_all=SUM(Sheet1[Category])
VAR __filtered_category_Wocolor=FILTER(Sheet1,Sheet1[Category]="Wocolor")
VAR __filtered_category_other=FILTER(Sheet1,Sheet1[Category]="Blue" || Sheet1[Category]="Red" || Sheet1[Category]="Green")
VAR __sum_for_Wocolor=
CALCULATE(
    SUM(Sheet1[MyValue]),
    __filtered_category_Wocolor
    )
VAR __sum_for_other=
CALCULATE(
    SUM(Sheet1[MyValue]),
    __filtered_category_other
    )
VAR __Result=__sum_for_Wocolor - __sum_for_other
RETURN __Result

 

willythecat's avatar
willythecat
Regular Visitor
2 years ago

Hi some_bih ,

thank you for your reply.

I probably explained the task incorrectly. In the sample above the result should be calculated in column F (with header Measure), where Measure = MyValue (Category = WOColor) - MyValue(Category = Blue) - MyValue(Category = Red) - MyValue(Category = Green) => 171.330.883,45 - 14.807.357,66 - 15.808.107,3 - 172.222.032,19 = -31.506.613,70

The calculation should be done for all Category = WOColor.

Thank you

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi willythecat for test 2 measure solution there will be 5 measures as following (only color for 3 is different)

    Kudos appreciated / accept solution.

     

    Sum of MyValue = SUM(Sheet1[MyValue])

     

     
    Sum of MyValue_red =
    CALCULATE(
        SUM(Sheet1[MyValue]),
        Sheet1[Category]="Red"
    )
    Sum of MyValue_green =
    CALCULATE(
        SUM(Sheet1[MyValue]),
        Sheet1[Category]="Green"
    )
    Sum of MyValue_blue =
    CALCULATE(
        SUM(Sheet1[MyValue]),
        Sheet1[Category]="Blue"
    )
     
     

     

    Test Measure v2 =
    VAR __selected_category=SELECTEDVALUE(Sheet1[Category])
    VAR __Result=SWITCH(
        TRUE(),__selected_category="Wocolor",[Sum of MyValue]-[Sum of MyValue_blue]-[Sum of MyValue_red]-[Sum of MyValue_green],0)
    RETURN __Result