Adding columns to the right of the total column in a matrix

[akkitek]

Hi Everyone,
I'm trying to add a couple of columns to the right of the Total column in a matrix. I need to make my matrix look exactly like the image below.

I have created a matrix by adding the weeks field in the columns section (image below) . I need to add contract rate and contract revenue fields to the right of the total column. 

I also tried creating this in a table visual instead of a matrix and pivioted the weeks field to get individual columns for every week but manually totalling the columns becomes hard and once the dashabord is published the new weeks data wouldn't get added into the visual. Any idea how I can achieve this? Please find attached my sample file below. Thanks!

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

[PaulDBrown]

You can either include the measures and hide the unwanted columns by each date (turn off Word Wrap in the formatting pane for columns and then drag the right edge of the column to the left),

or you can build a "custom" matrix. See if this helps:

https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1... 

In your case, to get the columns to the right, you must assign a really high index number (higher than the possible number of dates in the model).

[akkitek]

Hey @PaulDBrown ,
I like the idea of hiding unwanted columns by each date but when new weeks' data comes in, we'd have to hide them manually every week right? 
I wouldn't mind the custom matrix approach if it gets us to the desired output but I'm having some trouble creating the custom matrix, would it be possible for you to show me how its done in the attached sample file? 
Thanks for all the support:)

[PaulDBrown · Accepted solution]

"each date but when new weeks' data comes in, we'd have to hide them manually every week right?"

Yes, unfortunately

Here is one way of creating the custom matrix table for the layout:

Hybrid Matrix =
VAR _NumWeeks =
    DISTINCTCOUNT ( log_file[Week] ) * 10
VAR _Weeks =
    ADDCOLUMNS (
        DISTINCT ( log_file[Week] ),
        "Order", RANKX ( ALL ( log_file[Week] ), log_file[Week],, ASC, DENSE )
    )
VAR _Metrics = {
    ( "Total", _NumWeeks ),
    ( "Rate", _NumWeeks + 1 ),
    ( "Amount", _NumWeeks + 2 )
}
RETURN
    ADDCOLUMNS (
        UNION ( _Weeks, _Metrics ),
        "Week B", IF ( [Order] >= _NumWeeks, BLANK (), log_file[Week] )
    )

 I have added an extra column (Week B) to have the week dates formatted as actual dates, which you will need for the calcualtions.

Sorry, I couldn't move forward with the actual measure for the matrix since I don't know what you wish to calculate for each field.

Sample PBIX file attached