Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return value from column

I´m making a report sales, and I have a table like this...   IDCtmr   State   Company   Month   SaleType   Volume 001   NY   Microsoft   201801   Regular   50,000 001   NY   Mi...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We think the matrix visual should meet your requirement.

     

     

    In case you need a calculated table, you can use the following formula ( Consider of the length, I just put part of them)

     

    NewTable =
VAR montht =
    ADDCOLUMNS (
        DISTINCT ( 'Table'[Month] ),
        "index", RANKX ( DISTINCT ( 'Table'[Month] ), [Month],, ASC )
    )
VAR resultt =
    ADDCOLUMNS (
        DISTINCT (
            SELECTCOLUMNS (
                'Table',
                "IDCtmr", [IDCtmr],
                "State", [State],
                "Company", [Company]
            )
        ),
        "Mont_M1", MAXX ( FILTER ( montht, [index] = 1 ), [Month] ),
        "SaleType _M1", MAXX (
            FILTER (
                'Table',
                'Table'[Month]
                    = MAXX ( FILTER ( montht, [index] = 1 ), [Month] )
            ),
            [SaleType]
        ),
        "Volume_M1", SUMX (
            FILTER (
                'Table',
                'Table'[Month]
                    = MAXX ( FILTER ( montht, [index] = 1 ), [Month] )
            ),
            [Volume]
        ),
        "Mont_M2", MAXX ( FILTER ( montht, [index] = 2 ), [Month] ),
        "SaleType _M2", MAXX (
            FILTER (
                'Table',
                'Table'[Month]
                    = MAXX ( FILTER ( montht, [index] = 2 ), [Month] )
            ),
            [SaleType]
        ),
        "Volume_M2", SUMX (
            FILTER (
                'Table',
                'Table'[Month]
                    = MAXX ( FILTER ( montht, [index] = 2 ), [Month] )
            ),
            [Volume]
        )
    ADD MORE MONTH HERE
    )
RETURN
    resultt

     

     

    But it need to add cord for every month, so does the measure or calculated column.

     

     

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IDCtmr ", Int64.Type}, {" State ", type text}, {" Company ", type text}, {" Month ", Int64.Type}, {" SaleType ", type text}, {" Volume", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"IDCtmr ", " State ", " Company "}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"IDCtmr ", type text}}, "en-IN"),{"IDCtmr ", " State ", " Company ", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Attribute - Copy", "Index"}, {"Value", "Attribute - Copy", "Index"}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Expanded Partition", "Index", "Index - Copy"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column1", {{"Index", type text}}, "en-IN"),{"Index", "Attribute - Copy"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Index - Copy", type text}}, "en-IN"),{"Index - Copy", "Merged"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.2"),
    #"Sorted Rows" = Table.Sort(#"Merged Columns2",{{"Merged.2", Order.Ascending}, {"Merged.1", Order.Ascending}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Merged.2", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, true), {"Merged.2.1", "Merged.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.2.1", type text}, {"Merged.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Merged.2.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Merged.2.1", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Merged.2.1.1", "Merged.2.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.2.1.1", Int64.Type}, {"Merged.2.1.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Merged.2.1.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Merged.1]), "Merged.1", "Value"),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Pivoted Column", "Merged.2.1.2", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.2.1.2.1", "Merged.2.1.2.2", "Merged.2.1.2.3"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Merged.2.1.2.1", Int64.Type}, {"Merged.2.1.2.2", type text}, {"Merged.2.1.2.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Merged.2.1.2.1", "IDCtmr"}, {"Merged.2.1.2.2", "State"}, {"Merged.2.1.2.3", "Company"}})
in
    #"Renamed Columns"