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

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
Super User

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])))




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
vanessafvg
Super User
Super User

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])))




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thank you for help! 🙂

vanessafvg
Super User
Super User

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




If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Tahreem24
Super User
Super User

@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, Sales Analysis Report , and Fortune 500 Companies Analysis

@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
Super User

depends on how you want to use it

 

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

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors