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
     

     

     

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