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

[veerach]

Hi everyone, 

I have data like this

Area	Company	Product	Ordered Qty	Actual Qty
A	Matrix Inc	Pen	44	11
A	Matrix Inc	Paper	50	25
A	Matrix Inc	Book	10	7
A	Matrix Inc	Stand	16	12
A	Tech Co	Pen	20	16
A	Tech Co	Paper	35	15
B	Apptech	Book	15	5
B	Apptech	Stand	25	10
B	Apptech	Bench	10	7
B	Apptech	Pen	16	12
B	Z Inc	Paper	20	16
B	Z Inc	Book	35	15
B	Z Inc	Card	50	25

I have a requirement to show the data in this format

Area	Company Count	Total Ordered	Total Actual	% Actual/Ordered	Ordered Pen	Paper	Book	Stand	Bench	Card	Actual Pen	Paper	Book	Stand	Bench	Card
A	2	175	86	49%	66	85	10	16			27	41	7	12
B	2	171	90	53%	16	20	50	25	10	50	12	16	20	10	7	25

The products are dynamic in nature, I tried to use Matrix visual. Either I can do count, totals and percentage or only products. Can anyone suggest an approach to include both in same visual. (The idea here is user wants to see aggregate values by area, if he wants he can drill down to company level to see the details.)

Thanks

Veera

[v-kkf-msft · Accepted solution]

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.