Return value from column

[Anonymous]

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	Microsoft	201802	Regular	60,000
001	NY	Microsoft	201804	Irregular	80,000
001	NY	Microsoft	201805	Regular	10,000

I'm looking a DAX function that shows me the SaleType in a new column.

The final result must be:

IDCtmr

State

Company

Mont_M1

SaleType _M1

Volume_M1

Mont_M2

SaleType _M2

Volume_M2

Mont_M3

SaleType _M3

Volume_M3

Mont_M4

SaleType _M4

Volume_M4

001

NY

Microsoft

201801

Regular

50,000

201802

Regular

60,000

201804

Irregular

80,000

For the volume I've used Volume_M1=CALCULATE(SUM('Invoice'[Volume]),'Invoice'[YearMonth] IN { 201801 }), I also replied this for all the months, by now I can't find a functions that allows me do this with Month and SalesType.

My date is varchar, but there's no problem to convert this to int.

Can somebody help me with Month and SalesType. Please! 😞

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?

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.

[v-lid-msft · Accepted solution]

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]

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"