Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Add up Total cell value into List using Mcode

Hi,

 

 

I create this List as below from my data source and hope to add 'Total' value in a row which will be for 5G + LTE_M.

Then I could make below calculation for Total vs 5G or Total vs LTE_M.

Is there any Mcode I can forcely add up 'Total' value into automacically created List? 

*there will be more unique value which is unknown now apart from 5G and LTE_M in the future so I cannot create a table manually.

 

 

Total vs =
var _legend = SELECTEDVALUE('Total'[Frequency])
var _result = IF(_legend="Total", CALCULATE('Sum of XXXXXX ,ALL('Delivery & Fault Trend'[Frequency])), CALCULATE('Delivery & Fault Trend'[Sum of XXXXXX], 'Delivery & Fault Trend'[Frequency]=_legend))
return  _result
 
 
 

my desired output is 

1. Total

2. 5G

3. LTE_M

 

 

FYI,

let
Source = Table.Combine({#"TOSS service PO_master - Previous Backup Data", #"TOSS service PO_master - Current Weekly Data"}),
Frequency = Source[Frequency],
#"Removed Duplicates" = List.Distinct(Frequency)
in
#"Removed Duplicates"

General Comment
Need Help
Show and Tell
Tips and Tricks
  • danextian's avatar
    danextian
    1 year ago

    This is based on your original query

    let
Source = Table.Combine({#"TOSS service PO_master - Previous Backup Data", #"TOSS service PO_master - Current Weekly Data"}),
Frequency = Source[Frequency],
#"Removed Duplicates" = List.Distinct(Frequency),
#"Added Total row" = List.Combine({"Total"}, #"Removed Duplicates")
in
#"Added Total row"

3 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Anonymous 

     

    Try this:

    // Combine the text value "Total" with a distinct list of elements from the list "Frequency".
List.Combine(
    {"Total"},          // A single-element list containing the text value "Total".
    List.Distinct(      // Remove duplicate elements from the list "Frequency".
        Frequency       // The list from which duplicates are removed.
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    +I've succeeded creating as below, just pls suggest if there is any better way. 

     

     

    let
    Source = Table.Combine({#"TOSS service PO_master - Previous Backup Data", #"TOSS service PO_master - Current Weekly Data"}),
    Frequency = Source[Frequency],
    #"Removed Duplicates" = List.Distinct(Frequency),
    #"Converted to Table" = Table.FromList(#"Removed Duplicates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each "Total"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {}, "Attribute", "Value"),
    #"Removed Duplicates1" = Table.Distinct(#"Unpivoted Columns", {"Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates1",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Frequency"}})
    in
    #"Renamed Columns"

    • danextian's avatar
      danextian
      Super User
      1 year ago

      This is based on your original query

      let
Source = Table.Combine({#"TOSS service PO_master - Previous Backup Data", #"TOSS service PO_master - Current Weekly Data"}),
Frequency = Source[Frequency],
#"Removed Duplicates" = List.Distinct(Frequency),
#"Added Total row" = List.Combine({"Total"}, #"Removed Duplicates")
in
#"Added Total row"