Forum Discussion

plutoforever's avatar
plutoforever
Frequent Visitor
4 years ago
Solved

Need help with Matrix display

For the life of me I can't figure out a logical process to get what I need so trying here.  I have a table of data in the following format in BI (which is being generated by a SQL statement reading m...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    That's a great idea.  If you drop the [Sum of Unit Price] column and do some index trickery you can shoehorn everything into one visual

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5RCsAgDEPvUtiflMa26s4i3v8aU+eYDPYR0kdLk1oJAhQKpGKpG7qis5fNhXWuWOQYuASGb/ivFippRkxib5Cb8XBl2HiDfsA5j2mGnp+0LdVmJ5E43R/EXbVsCD6x0Ki1Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer #" = _t, #"EF Ticket #" = _t, Units = _t, #"Sum of Unit Price" = _t, Subtotal = _t, #"Sales Tax" = _t, #"Sales Tax %" = _t, #"Federal Excise Tax" = _t, #"Federal Excise Tax Rate" = _t, #"State Road Tax" = _t, #"State Road Tax Rate" = _t, #"Federal Oil Spill Fee" = _t, #"Federal Oil Spill Fee Rate" = _t, #"Federal LUST Fee" = _t, #"Federal LUST Fee Rate" = _t, #"State Agriculture Inspection Fee" = _t, #"State Agriculture Inspection Fee Rate" = _t, #"State Transport Load Fee" = _t, #"State Transport Load Fee Rate" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Sales Tax %", "Sales Tax Rate"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sum of Unit Price", Currency.Type}, {"Units", type number}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Customer #", "EF Ticket #", "Units", "Subtotal", "Sales Tax", "Sales Tax Rate", "Federal Excise Tax", "Federal Excise Tax Rate", "State Road Tax", "State Road Tax Rate", "Federal Oil Spill Fee", "Federal Oil Spill Fee Rate", "Federal LUST Fee", "Federal LUST Fee Rate", "State Agriculture Inspection Fee", "State Agriculture Inspection Fee Rate", "State Transport Load Fee", "State Transport Load Fee Rate"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Customer #", "EF Ticket #"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Rate", each if Number.IsEven([Index]) then #"Added Index"{[Index]+1}[Value] else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [Index],each Number.Mod([Index]+14,16),Replacer.ReplaceValue,{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Index]>13 or [Rate] <> null),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows",each [Rate],each if [Index]=14 then null else [Rate],Replacer.ReplaceValue,{"Rate"})
in
    #"Replaced Value1"

     

     

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

Seeing lbendlin  & VijayP  suggestions to do this in Power Query, I would venture that it's porbably the best route.

As an alternative, I have worked through this solution (probably more an academic rather than practical solution) which unfolds into the creation of a physical table (hence why staying in Power Query makes way more sense).

The solution involves creating two unrelated tables (one from the fact table and the other using the "enter Data" option):

Apart from the basic sum measures for each value column, you need the following to create the final table:

Metric Calculation =
VAR _Row =
    SELECTEDVALUE ( 'Row Table'[Index] )
VAR _Column =
    SELECTEDVALUE ( HeaderTable[Index] )
VAR _Val =
    SWITCH (
        TRUE (),
        AND ( _Row = 1, _Column = 1 ), [Sales Tax],
        AND ( _Row = 1, _Column = 2 ), [Sales Tax %],
        AND ( _Row = 2, _Column = 1 ), [Federal Excise Tax],
        AND ( _Row = 2, _Column = 2 ), [Federal Excise Tax Rate],
        AND ( _Row = 3, _Column = 1 ), [State Road Tax],
        AND ( _Row = 3, _Column = 2 ), [State Road Tax Rate],
        AND ( _Row = 4, _Column = 1 ), [Federal Oil Spill fee],
        AND ( _Row = 4, _Column = 2 ), [Federal Oil Spill fee Rate],
        AND ( _Row = 5, _Column = 1 ), [Federal LUSt fee],
        AND ( _Row = 5, _Column = 2 ), [Federal LUSt fee Rate],
        AND ( _Row = 6, _Column = 1 ), [State Agricultural Inspection Fee],
        AND ( _Row = 6, _Column = 2 ), [State Agricultural Inspection Fee Rate],
        AND ( _Row = 7, _Column = 1 ), [State Transport Load Fee],
        AND ( _Row = 6, _Column = 2 ), [State Transport Load Fee Rate]
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            VALUES ( 'Row Table'[Metric] ),
            "@value", IF ( _Val = 0, BLANK (), _Val )
        ),
        [@value]
    )

You can now create the physical table using:

Metric Table =
VAR H1 =
    SELECTCOLUMNS (
        FILTER ( VALUES ( 'HeaderTable'[Header] ), HeaderTable[Header] = "Amounts" ),
        "Amounts", HeaderTable[Header]
    )
VAR H2 =
    SELECTCOLUMNS (
        FILTER ( VALUES ( 'HeaderTable'[Header] ), HeaderTable[Header] = "Rates" ),
        "Rates", HeaderTable[Header]
    )
VAR _Amounts =
    ADDCOLUMNS (
        CROSSJOIN (
            SUMMARIZE ( 'DataTable', 'DataTable'[Customer #], 'DataTable'[EF Ticket #] ),
            SUMMARIZE ( 'Row Table', 'Row Table'[Metric], 'Row Table'[Index] ),
            H1
        ),
        "@amount", [Metric Calculation]
    )
VAR _Rates =
    ADDCOLUMNS (
        CROSSJOIN (
            SUMMARIZE ( 'DataTable', 'DataTable'[Customer #], 'DataTable'[EF Ticket #] ),
            SUMMARIZE ( 'Row Table', 'Row Table'[Metric], 'Row Table'[Index] ),
            H2
        ),
        "@rate", [Metric Calculation]
    )
RETURN
    NATURALLEFTOUTERJOIN ( _Rates, _Amounts )

To get 

which can of course be cleaned up by removing redundant columns and rows with empty values. Finally just set up the visual and use a dimension table for Customers to filter both this table and the main fact table if need be.

 

I've attached the sample PBIX file