Iterate the values and do automate calculation

[dshah12]

In the below table I want that if it has string (P) (MFA) it should do the sum and put that value in the (M) (MFA) actual and should eliminate counting the (M) (RR) 

such that new values should be  (Table wanted)

Applica no	Title	Actual	Forecast
1	(M) (MFA) BA MOU (P)	10,600	400000000

The old table :-

Applica no	Title	Actual	Forecast
1	(M) (MFA) BA MOU (P)		400000000
2	(P) (MFA)  BA  MOU - PLL	5000
3	(P) (MFA)  BA  MOU - PLA	5600
4	(M) (RR) BCA	6000

[v-yohua-msft]

Hi, @dshah12
Based on the information you gave, I have created a table

Please follow these steps:

1. Create a new column and named  Actual

2. Then use the following DAX expression.

Actual =

IF('Table'[Title] = "(M) (MFA) BA MOU (P)",

CALCULATE(SUM('Table'[value]),

FILTER('Table', CONTAINSSTRING('Table'[Title],"(P) (MFA)")

)))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUdLw1VTQ8HVz1FRwclTw9Q9V0AjQBAorALGJARQoxepEKxmBFAfAFINUg5XrKgT4+IBUm4IUgjSCFBvjVuwIUmuGpNYE5oqgIKAjnEHyZnCzYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Applica no" = _t, Title = _t, Actual = _t, Forecast = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actual", Currency.Type}, {"Forecast", Currency.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not Text.Contains([Title], "(M) (RR)")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {}, { {"Applica no", each List.Min([Applica no]), type nullable number},{"Title", each List.First([Title]), type nullable text}, {"Actual", each List.Sum([Actual]), type nullable Currency.Type}, {"Forecast", each List.Sum([Forecast]), type nullable Currency.Type}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".