Forum Discussion

dshah12's avatar
dshah12
Helper II
2 years ago

Iterate the values and do automate calculation

 

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 noTitleActualForecast
1(M) (MFA) BA MOU (P)10,600400000000

 

The old table :-

Applica noTitleActualForecast
1(M) (MFA) BA MOU (P) 400000000
2(P) (MFA)  BA  MOU - PLL 5000 
3(P) (MFA)  BA  MOU - PLA5600 
4(M) (RR) BCA6000 
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

     

    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".

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

     

     

     

     

    1. 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.