Matrix visual - Regular and Dynamic columns - is this possible?

Hi veerach ,

 

1. Please unpivot the Ordered Qty and the Actual Qty columns.

 

2. Create the new table: MatrixTable.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcs7PLUjMq1Rwzi/NK1GK1QELhuSXJOYo+BelpBalpqAKOiaXlCbmwMRUoXx9uNpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Product = _t]),
    #"Appended Query" = Table.Combine({Source,Table.SelectColumns(Table,{"Category","Product"})}),
    #"Removed Duplicates" = Table.Distinct(#"Appended Query")
in
    #"Removed Duplicates"

 

3. Create the table: SlicerTable.

 

let
    Source = Table.SelectColumns(Table,"Product"),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "MatrixColumns", each Table.Distinct( Table.SelectColumns(MatrixTable,"Product") )),
    #"Expanded MatrixColumns" = Table.ExpandTableColumn(#"Added Custom", "MatrixColumns", {"Product"}, {"MatrixColumns"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded MatrixColumns", each ([MatrixColumns] = "% Actual/Ordered" or [MatrixColumns] = "Company Count" or [MatrixColumns] = "Total Actual" or [MatrixColumns] = "Total Ordered" or [MatrixColumns] = [Product]))
in
    #"Filtered Rows"

 

4. Create the relationship.

 

 

5. Create the measure.

 

Measure = 
VAR filtertab =
    TREATAS ( VALUES ( MatrixTable[Product] ), 'Table'[Product] )
RETURN
    SWITCH (
        MAX ( MatrixTable[Category] ),
        "Ordered Qty", CALCULATE ( [Total Ordered], filtertab ),
        "Actual Qty", CALCULATE ( [Total Actual], filtertab ),
        SWITCH (
            MAX ( MatrixTable[Product] ),
            "Company Count", [Company Count],
            "Total Ordered", [Total Ordered],
            "Total Actual", [Total Actual],
            "% Actual/Ordered", FORMAT ( [% Actual/Ordered], "Percent" )
        )
    )

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.