cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ANM_97
Helper III
Helper III

DECREASE THE VALUE AND ADD THE VALUE ACCORDING TO CATEGORY

Hi!

I have the following situation: I have 3 categories (A, B ,C) , their value and loss. I have to DEDUCT the loss from category A AND ADD IT TO category C.

 

EXAMPLE:

 CATEGORY VALUELOSS
 A 10020
 B 1200
 C 500
 TOTAL 27020

 

EXPECTED RESULT:

 

 CATEGORY VALUELOSS
 A 800
 B 1200
 C 7020
 TOTAL 27020

WHAT MEASURE COULD I BUILD?

 

THANKS! ~~

 

1 ACCEPTED SOLUTION
vanessafvg
Super User I
Super User I

try this.
 
result_test =
var VALA = CALCULATE(sum(Test[LOSS]), FILTER(ALLSELECTED(Test),Test[CATEGORY] ="A"))
return
IF(SELECTEDVALUE(Test[CATEGORY]) = "A", SUM(Test[VALUE]) - SUM(Test[LOSS]),
if(SELECTEDVALUE(Test[CATEGORY]) = "C", sum(Test[VALUE]) + VALA,
sum(Test[VALUE])))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
vanessafvg
Super User I
Super User I

try this.
 
result_test =
var VALA = CALCULATE(sum(Test[LOSS]), FILTER(ALLSELECTED(Test),Test[CATEGORY] ="A"))
return
IF(SELECTEDVALUE(Test[CATEGORY]) = "A", SUM(Test[VALUE]) - SUM(Test[LOSS]),
if(SELECTEDVALUE(Test[CATEGORY]) = "C", sum(Test[VALUE]) + VALA,
sum(Test[VALUE])))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thank you for help! 🙂

vanessafvg
Super User I
Super User I

LossC=
var lossA = CALCULATE(sum('Table'[LOSS]), 'Table'[CATEGORY] = "A")
var valC = CALCULATE(sum('Table'[VALUE]), 'Table'[CATEGORY] = "C")
return valC + lossA
 
lossA=
var lossA = CALCULATE(sum('Table'[LOSS]), 'Table'[CATEGORY] = "A")
var valA = CALCULATE(sum('Table'[VALUE]), 'Table'[CATEGORY] = "A")
return valA - lossA




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Tahreem24
Super User II
Super User II

@ANM_97 , Need to create measures like this:

NEW VALUE  = SUM(Table[Value])-SUM(Table[Loss])

NEW LOSS = 0 

OR NEW LOSS MEASURE = SUM(Table[Loss])-SUM(Table[Loss])

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

@Tahreem24 

It's not that simple. The value of LOSS = 20 must be subtracted from A and added to category C. I have attached a measure, but it does not add to category C.

 

ANM_97_1-1616759032445.png

 

vanessafvg
Super User I
Super User I

depends on how you want to use it

 

result  = sum('Table'[VALUE]) - sum('Table'[LOSS])

RESULT 2 = SUMX ( table, 'table'[value] - 'table'[loss] )





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@vanessafvg 

It's not that simple. The value of LOSS = 20 must be subtracted from A and added to category C. I have attached a measure, but it does not add to category C.

ANM_97_0-1616759006894.png

 

I try this: 

ANM_97_0-1616760487206.png

But don't work ok, because the LOSS value is also low from the total value.

 

I am not sure why you are asking for someone else to come and help when I am putting my time into helping you?    Please provide the code  you have used in text form so i can see what is going on





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I appreciate the time you give. With the solution sent by you, I did not get the expected result. It would help me if you tested the measure before answering. I tried many options. This is the measure from the picture:
 
 
result_test =
VAR lossA = CALCULATE( sum(test[LOSS]), test[CATEGORY] = "A")
VAR valC = CALCULATE( sum(test[VALUE]), test[CATEGORY] = "B")
return
IF(FIRSTNONBLANK(test[CATEGORY],"") ="A", CALCULATE(SUM(test[VALUE]) - lossA),
IF(FIRSTNONBLANK(test[CATEGORY], "") ="C", CALCULATE(valC + lossA), SUM(test[VALUE])))

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors