cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

add and subtract according to the formula in the column

Please help

I have a table whose one column is the ID and other formula (can be different lengths): 

ID  Formula                    Name                  

1    40-50+2025                    Reason1     

2    30-30+45+60-789           Reason2

......

Also I have an account table:

ID   account amount        BussinesUnitID

1     40           200                 1

2     402          100                 3

3    50               20                 2

4      2025        30                 3

5     30               60               2

6     45              70                  2

7      4036         90                 1

8       301          40               2

9       60               50            1

10     789           20               1

 .....

From all of this I need to get table like that: (but must respond to the selected bussines units in the filter)

If all bussines units are selected in the filter

Name   amount       

Reason1 400

Reason2    100

 

How it should be calculated:

Reason1 have formula 40-50+2025 so 

40 (all accounts whose start numbers like 40 sum) -- > 200+100+90+390 

50 (all accounts whose start numbers like 50 sum) --> 20

2025 (all accounts whose start numbers like 2025 sum) --> 30

So by formula 40-50+2025 reason1 will be 400 (390-20+30)

 

But if just a few bussines units are selected ( like 1 ,2 ) table shoud be:

Name   amount 

Reason1 270

Reason2    100

How it should be calculated:

Reason1 have formula 40-50+2025 so 

40 (all accounts whose start numbers like 40 sum and BussinesUnitID is 1 or 2) -- > 200+90+290 

50 (all accounts whose start numbers like 50 sum and BussinesUnitID is 1 or 2) --> 20

2025 (all accounts whose start numbers like 2025 sum and BussinesUnitID is 1 or 2) --> 0

So by formula 40-50+2025 reason1 will be 270 (290-20+0)

Have any ideas how to do this? 😞

4 REPLIES 4
Super User IV
Super User IV

@tiufea , Try a measure like

calculate(sum(Table[amount]), filter(Table, left(Table[account],2) = "40"))
- calculate(sum(Table[amount]), filter(Table, left(Table[account],2) = "50"))
- calculate(sum(Table[amount]), filter(Table, left(Table[account],4) = "2025"))

 

Or create a column like this sum that up

reason 1 = Switch( True() ,
left(Table[account],4) = "2025" || left(Table[account],2) = "40" , 1*Table[amount] ,
left(Table[account],2) , -1 * Table[amount]
)

 

reason 1 measure = sumx(Table, Switch( True() ,
left(Table[account],4) = "2025" || left(Table[account],2) = "40" , 1*Table[amount] ,
left(Table[account],2) , -1 * Table[amount]
))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

The problem is that in first table there can be many values, and it's not enough to count just reason1.. I need universal formula which would fit all the rows in the column and calculate reason1, reason2, reason3 .... reasonN. First table have many different values like:

ID  Formula                    Name                  

1    40-50+2025                    Reason1     

2    30-30+45+60-789           Reason2

3    50+60-90+40           Reason3

4   458+68-698           Reason4

5    45+60-90+98-85+65           Reason5

6    45+60-789           Reason6

N    45+-8960-789           ReasonN

And I need that it respond to a filter BussinesUnitID where the values are chosen by the person himself in visualizations slicer

 

@tiufea , every reason has different rule. are those rules stored somewhere ? If not I doubt yu can make it generic



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

No, they have the same rules just have to pick up different account numbers and there are different numbers of items. They are stored in the first table as I tried to show in the example

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors