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     Total Jan Feb USA 100 60 40     Thanks, PBI V2
  • eliasayyy's avatar
    3 years ago

    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