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 different filter for all the substracted values. I have four columns with (Date, Branch, CatType and Category) and a value field (MyValue). I want to build substraction for all Category = WOColor with same filter for Date, Branch, CatType and subtract values with same filter by Date, Branch, CatType but different Category as follows:

Measure = MyValue (Category = WOColor) - MyValue(Category = Blue) - MyValue(Category = Red) - MyValue(Category = Green)

applying for all MyValues the same filter for Date, Branch, CatType. 

Sample data:

 

Is there a way to create a measure for it in Power BI?

Thanks for all your efforts in advance.

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
     

     

     

3 Replies

  • 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