add and subtract according to the formula in the column

[Anonymous]

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? 😞

[amitchandak]

@Anonymous , 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]
))

[Anonymous]

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

[amitchandak]

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

[Anonymous]

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