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  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_M1Mont_M2 SaleType _M2 Volume_M2Mont_M3 SaleType _M3 Volume_M3Mont_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! :(

 

 

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.

3 Replies

  • 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"
  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    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.

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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.