Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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:
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()
))
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.
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:
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"
Table =
SELECTCOLUMNS('Merge1',"User ID",[Employee_Details.userid],"Date",[timeslip_date],"Value",[rc_hrs])
Total =
var _total=
SUMMARIZE(
'Table',[User ID],
"Date"," Total",
"Value",SUM('Table'[Value] ))
var _union=
UNION('Table',_total)
return
_union
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.
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
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:
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:
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()
))
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.
Thank you so much. I really appreciate it
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:
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.
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.
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
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.
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
@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.
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.
@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'.
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |