Can you add calculated columns to end of matrix?

[Grizle]

Hi Everyone,

I have a matrix table and I want to add some calculated columns to the end of it. These are summary columns and should not appear under each of the current columns. 

Here is a basic example of what my data might look like:

Division	Specs	Values
1	sales	500
1	margin	20%
1	budget	100
2	sales	400
2	margin	30%
2	budget	75
40	sales	1000
40	margin	15%
40	budget	99

FYI in my data there are many divisions (1000+) and specs (100+). I simplified the date example above. 

We have a series of filters allowing the user to select a more manageable amount of divisions and specs. 

I was able to add a total summary field using the built in total for a Matrix visual in Power BI. So our current matrix outputs similar to this:

Specs	1	2	40	Total
Sales	500	400	1000	1900
Margin	20%	30%	15%	65%
Budget	100	75	99	274

However we also want to add additional summary columns at the end (far right) of the matrix. Ideally we want something like this:

Specs	1	2	40	Total	Std Dev	Mean
Sales	500	400	1000	1900	321.46	633.33
Margin	20%	30%	15%	65%	7.64%	21.67%
Budget	100	75	99	274	14.15	91.33

I was able to build measures but if I add them to the values in my matrix they appear under each of the Divisions, and I don't want that. I need them setup as summary columns on the far right. 

Is a matrix table even capable of adding in these columns? Should I be doing this using a custom table in Dax?

Any tips, references, or advice would be greatly appreciated.

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Grizle 

You can use power query , put the following code in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSpOzEktBtKmBgZKsToQsdzEovTMPCDDyEAVLphUmpKeWgJkGEJVGiHpNkESg+s2huo2QtZtbgoWMzFA0g000QAhCtdvaKqKEIUbYGmpFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Division = _t, Specs = _t, Values = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Specs", type text}, {"Values", type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Division", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Division", type text}}, "en-US")[Division]), "Division", "Values"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Total", each [1]+[2]+[40]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Mean", each [Total]/3)
in
    #"Added Custom1"

Output:

Best Regards!

Yolo Zhu

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

[FreemanZ]

hi @Grizle 

This is not advisible, but still possibe.

Option1:

create multiple measures and plot with the measures instead of the columns

1 = 
CALCULATE(
    SUM(tbl1[Values]),
    tbl1[Division]=1
)
2 = 
CALCULATE(
    SUM(tbl1[Values]),
    tbl1[Division]=2
)
40 = 
CALCULATE(
    SUM(tbl1[Values]),
    tbl1[Division]=40
)
Total = SUM(tbl1[Values])

Option2: pivot the Division column in Power Query, then add the total column with this:

Total2 = [1]+[2]+[40]  //with DAX or in PQ

you will get this:

Considering the 1000+ division you have. none of them is advisible. Try to decompose the table and plot in separate visuals.