topic Re: Subtract multiple values from same column using different filter and rows in DAX Commands and Tips

Subtract multiple values from same column using different filter and rows

willythecat — Sat, 02 Sep 2023 11:12:03 GMT

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.

Re: Subtract multiple values from same column using different filter and rows

some_bih — Sat, 02 Sep 2023 19:51:41 GMT

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

Re: Subtract multiple values from same column using different filter and rows

willythecat — Sun, 03 Sep 2023 15:01:20 GMT

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

Re: Subtract multiple values from same column using different filter and rows

some_bih — Sun, 03 Sep 2023 17:15:11 GMT

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