Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Total should come first column

Hello All,

 

We are looking for a solution where in a matrix we can show total as First. like below

 

 TotalJanFeb
USA1006040

 

 

Thanks,

PBI V2

  • its not possible from the format however there is a workaround

    lets say this is my table


    then you need to create a new table that will add "Total" to each country as well as a sorting so total can be in front 

    NewTable = 
    VAR OriginalWithSort = ADDCOLUMNS(
        SELECTCOLUMNS(
            'Table',
            "Country", [Country],
            "Month", [Month],
            "Amount", [Amount]
        ),
        "SortOrder", SWITCH([Month], "Jan", 2, "Feb", 3, "Mar", 4, "Apr", 5, "May", 6, "Jun", 7, "Jul", 8, "Aug", 9, "Sep", 10, "Oct", 11, "Nov", 12, "Dec", 13, 1)
    )
    
    VAR TotalWithSort = SELECTCOLUMNS(
        SUMMARIZE(
            'Table', 
            [Country], 
            "TotalAmount", SUM('Table'[Amount])
        ),
        "Country", [Country],
        "Month", "Total",
        "Amount", [TotalAmount],
        "SortOrder", 1
    )
    
    RETURN
    UNION(
        OriginalWithSort,
        TotalWithSort
    )


    now sort the "Month" Column by the "Sort" column 

    add the fields in a new table visual from the newly created table above and turn off from format the column subtotal

    result


     



4 Replies

  • eliasayyy's avatar
    eliasayyy
    Memorable Member

    its not possible from the format however there is a workaround

    lets say this is my table


    then you need to create a new table that will add "Total" to each country as well as a sorting so total can be in front 

    NewTable = 
    VAR OriginalWithSort = ADDCOLUMNS(
        SELECTCOLUMNS(
            'Table',
            "Country", [Country],
            "Month", [Month],
            "Amount", [Amount]
        ),
        "SortOrder", SWITCH([Month], "Jan", 2, "Feb", 3, "Mar", 4, "Apr", 5, "May", 6, "Jun", 7, "Jul", 8, "Aug", 9, "Sep", 10, "Oct", 11, "Nov", 12, "Dec", 13, 1)
    )
    
    VAR TotalWithSort = SELECTCOLUMNS(
        SUMMARIZE(
            'Table', 
            [Country], 
            "TotalAmount", SUM('Table'[Amount])
        ),
        "Country", [Country],
        "Month", "Total",
        "Amount", [TotalAmount],
        "SortOrder", 1
    )
    
    RETURN
    UNION(
        OriginalWithSort,
        TotalWithSort
    )


    now sort the "Month" Column by the "Sort" column 

    add the fields in a new table visual from the newly created table above and turn off from format the column subtotal

    result


     



  • Anonymous's avatar
    Anonymous
    Not applicable

    Hello eliasayyy ,

    Thanks for response, Is there any performance issue with this approch if case we have large volume of dataset.

    Hello mlsx4 

    Thanks for response,

    Is there any performace issue, we have create many to many relationship between tables in case we have large volume of dataset.

     

    Thanks,

    PBI V2

    • eliasayyy's avatar
      eliasayyy
      Memorable Member

      hello no it shouldnt create any issues with performance because you are just creating a new table so it is only calculated once