Create a matrix from a data table by type, types that do not exist today

[Chateauunoirr]

Hello, I have the Project object from Salesforce, which has on the same line for each project, initial and completed amounts for types X and Y.

I want to display in matrix format the progress of the project, the amounts excepted and completed.

As seen below a current representation, of the data that I have today is what I want to do as a display, what would be the best method to choose?

[danextian]

Is that really the current representation of your data? I am asking because a lot of users  have posted oversimplified data, were given a working solution based on the sample data only to come back later saying the data was more complex than the users made it appear.

[Chateauunoirr]

@danextian Hi, of course this is a simplified form but there are just more than two types "X,Y", so the method I would apply for both will apply for my model with 4 types "X,Y,Z,T"

[Chateauunoirr]

Hello, thank you very much for your help the only problem is that my projects table is much more complex and is linked to a lot of tables I can not make changes directly on it. Do you think that doing it via a copied project table or doing it via DAX is possible?

[Ritaf1983]

Hi @Chateauunoirr 

I doubt that DAX is intended for table transformations; this is part of ETL processes that should be handled either at the data source before importing into Power BI or in Power Query (PQ).

There is a video about performing an Unpivot using DAX, but again, I’m not sure where this approach can lead from there.

https://www.youtube.com/watch?v=9Xv8COs59tc

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

[Ritaf1983 · Accepted solution]

Hi @Chateauunoirr 
At the first step (s} you need to transform your table to unpivoted format.

The m code for this :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooys9KTS5RcASyDQ0MDCAUkDSCcIyBZKxOtJIRklonINsUIm0C0wGnlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Project Name" = _t, #"Expected Amount X" = _t, #"Realized Amount X" = _t, #"Expected Amount Y" = _t, #"Realized Amount Y" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Project Name", type text}, {"Expected Amount X", Int64.Type}, {"Realized Amount X", Int64.Type}, {"Expected Amount Y", Int64.Type}, {"Realized Amount Y", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id", "Project Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "sub project"}, {"Attribute.1", "status"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "status", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"status.1", "status.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"status.1", type text}, {"status.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"status.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"status.1", "status"}})
in
#"Renamed Columns1"

Or you can apply the needed step from UX of query editor , I attached the pbix to the solution so you can follow:

After closing and applying you can create 3 DAX measures :

expected =

var expected_= FILTER('Table','Table'[status]="expected")

RETURN

SUMX(expected_,'Table'[Value])

Realized =

var realized_= FILTER('Table','Table'[status]="realized")

RETURN

SUMX(realized_,'Table'[Value])

Now just create a wanted matrix:

The pbix is attached

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