Is there any method or tick to get the total column on front instead of in the end?

[bikelley]

Hello,

Can anybody please tell how to get the Total column in front? Please see the image below that I need next to my name columns. 

Thank you so much 

[v-robertq-msft]

Hi, @bikelley 

According to your sample file, I found that the User ID and [rc_hrs] are in the different tables, I think you should first combine them into one table in order to use UNION() and Summarize() function to get the correct Total value, you can try my steps:

1. Go to the Power query, merge query like this:

Then expand and remove columns to get a table like this:

This is the M query in the advanced editor, you can copy them and parse them into yours:

let
    Source = Table.NestedJoin(Recorder_User, {"recorder_user_sk"}, Employee_Details, {"employee_sk"}, "Employee_Details", JoinKind.LeftOuter),
    #"Expanded Employee_Details" = Table.ExpandTableColumn(Source, "Employee_Details", {"userid"}, {"Employee_Details.userid"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Employee_Details",{"recorder_user_sk"})
in
#"Removed Columns"

2. Create a calculated table to change the order of columns in the merged table:

Table =

SELECTCOLUMNS('Merge1',"User ID",[Employee_Details.userid],"Date",[timeslip_date],"Value",[rc_hrs])

3. Create a Total table:

Total =

var _total=

SUMMARIZE(

    'Table',[User ID],

    "Date"," Total",

    "Value",SUM('Table'[Value] ))

var _union=

UNION('Table',_total)

return

_union

4. Create a Matrix chart and place it like this:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bikelley]

@v-robertq-msft 

This is awesome, thank you so much for the detailed explanation.  

One last thing , I just want to make sure. Is there any way I can change the Date Type to Date? right now it's showing as text. if I change it, it will break. The reason is I need to join this table to the Calendar table so we can do the filtering. 

Another thing is the bottom grand total is not correct, do you know why is that? but this is not important, the date is the one i need the most. 

Again, thank you so much 

[v-robertq-msft · Accepted solution]

Hi, @bikelley 

In my opinion, you can’t change the type of [Date] to “Date”, because it contains the value “ Total”, which performs as the total column in your Matrix, and one column can’t have two types of value, if you want to use a Date slicer to slice the data within the Matrix, I figured out a method that Intercepts the string of the date field to match the date in the slicer, you can try my steps:

1. Create three calculated columns to get the day, month year within the [Date] column:

Day =

IF([Date]=" Total",BLANK(),

IF(MID([Date],5,1)="/",VALUE(MID([Date],3,2)),VALUE(MID([Date],3,1))))

Month =

IF([Date]=" Total",BLANK(),

VALUE(LEFT([Date],1)))

Year =

IF([Date]=" Total",BLANK(),

VALUE(RIGHT([Date],4)))

The output can be like this:

2. Delete the relationship between ‘Date’ table and ‘Total’ table:

3. Create a measure like this:

Value1 =

var _date=DATE(MAX([Year]),MAX([Month]),MAX([Day]))

var _maxdate=MAXX(ALLSELECTED('Date'),[Date])

var _mindate=MINX(ALLSELECTED('Date'),[Date])

return

IF(

    MAX([Date])=" Total",SUM([Value]),

    IF(_date>=_mindate&&_date<=_maxdate,SUM([Value]),

    BLANK()

))

4. Then go to the report page, create a date slicer, and replace the [Value] column in the Matrix with a measure [Value1]:

And you can get what you want.

You can download my test pbix file here

According to the bottom grand total incorrect, I can’t find this problem in my Matrix, you can go to my pbix file and check again.

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bikelley]

Thank you so much. I really appreciate it 

[v-robertq-msft]

Hi, @bikelley 

According to your description and sample picture, you want to make the total column in the front of your Matrix instead of in the end. I think you can use some unusual operations to achieve this because there’s no option to set this in Power BI now. I used a calculated table to achieve this, you can take a look at my method:

This is my test data based on your Matrix:

1. I created a calculated table:

Total =

var _total=

SUMMARIZE(

    'Table',[Name],

    "Date"," Total",

    "Value",SUM('Table'[Value] ))

var _union=

UNION('Table',_total)

return

_union

The output of this table is like this:

As we can see, I added three rows at the end of my original data table, and give them “ Total” as the Date value. The blank character before the “Total” can make it become the first column in the Matrix because the columns in the Matrix of Power BI are sorted by the first character ascending.

2. Then I created a Matrix and place it like this:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bikelley]

@v-robertq-msft 

Hello, I tried this but did not work, maybe I am using three tables. Anyway, I have attached a sample file below. Please take a look if you have time. It will be really nice to have it in front. Again, thank you so much for your time and help. I really appreciate it.  

Sample File
https://drive.google.com/file/d/1T6AtARL4piHsraHuVQ6GSR_FGwS_swLJ/view?usp=sharing

[v-robertq-msft]

Hi, @bikelley 

I’ve downloaded and opened the pbix file you posted, but I can’t find the Matrix you created before and the data table doesn't seem to be correct, like this:

I suggest you to check the pbix file you posted and share the correct one if it’s possible.

Thank you!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bikelley]

@v-robertq-msft 

I am really sorry, somehow I got the wrong link. Please see below for the correct one. 

https://drive.google.com/file/d/1T6AtARL4piHsraHuVQ6GSR_FGwS_swLJ/view?usp=sharing 

Thank you so much

[bikelley]

@v-robertq-msft 

Thank you so much for the post and your time. I really appreciate it.  I Just saw your post, I will work on this tomorrow morning and keep you posted. Again, this is awesome and thank you. 

[v-robertq-msft]

Hi, @bikelley 

OK, thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@bikelley If the 'Total' column is PBI generated, you can't change its position. You can write a measure for "Total'. Drop it in Values and adjust its position before 'Sub'.

[bikelley]

@Anonymous 

Thank you for the response. I know Total is Power BI generated, but I am wondering if I can move it to the front. 

Can you please tell me how to wire a measure that only shows the grand total? I don't want to see it every single day date. Any idea on that?