Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
plutoforever
Frequent Visitor

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 multiple system tables):

 

Customer #EF Ticket #UnitsSum of Unit PriceSubtotalSales TaxSales Tax %Federal Excise TaxFederal Excise Tax RateState Road TaxState Road Tax RateFederal Oil Spill FeeFederal Oil Spill Fee RateFederal LUST FeeFederal LUST Fee RateState Agriculture Inspection FeeState Agriculture Inspection Fee RateState Transport Load FeeState Transport Load Fee Rate
101183046125.5825.580.361.00%001.15000000000
37126043046544.63.1451712.771589.00%00001.140.0020.540.0010.380.0011.910.004

 

I have two Matrices in the following format (the #s showing are after the user has selected EF Ticket # 3046 and Customer # 3712604 from slicers reading the above table): 

plutoforever_0-1648671965236.png

Which I've set up as such: 

plutoforever_1-1648671998078.png

plutoforever_2-1648672728266.png

 

However there are two things I want to be able to do that I cannot figure out: 

 

1) Display the Totals and Rates in one Matrix side by side 

2) Suppress Rows where the values are 0 (such as Federal Excise Tax from the example above)

 

This is a sample mocked up in Excel of what I want the Matrix to display as in BI:

 

 AmountsRates
Sales Tax158.009.00%
Federal LUST Fee0.540.001
Federal Oil Spill Fee1.140.002
State Agriculture Inspection Fee0.380.001
State Transport Load Fee1.910.004
Subtotal1712.77 
Units544.60 

 

Any help would be appreciated

1 ACCEPTED SOLUTION

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"

 

lbendlin_0-1649028092318.png

 

View solution in original post

5 REPLIES 5
PaulDBrown
Community Champion
Community Champion

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):

header.jpgRow Table.jpg

model1.jpg

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 

metrics table.jpgwhich 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.
TM.gif

 

I've attached the sample PBIX file

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






VijayP
Super User
Super User

@plutoforever 

You need to unpivot alll value columns into one Column

and then load data to data model and then you can get result the way you want!




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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"

 

lbendlin_0-1649028092318.png

 

Thank you both that worked perfectly: 

plutoforever_0-1649166223859.png

And to answer your question on units in most cases they are in gallons so a fraction would be part of a gallon filled

 

lbendlin
Super User
Super User

You need to massively change the structure of your source data. Note that each column needs to have the same format so your Rates column has to either be all percentages, all decimals, or all text.  You also need to rename "Sales Tax %"  to "Sales Tax Rate"  to make this work.

 

(out of curiosity - how can you have fractions of units?)

 

 

 

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}, {"Subtotal", Currency.Type}, {"Units", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer #", "EF Ticket #", "Units", "Sum of Unit Price", "Subtotal"}, "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),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Rate] <> null))
in
    #"Filtered Rows"

 

 

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

 

This would then yield a display like this (note that everything is formatted as text)

 

lbendlin_0-1648947747063.png

You can then filter out all the rows where the value is 0

lbendlin_1-1648947813235.png

 

but you can see that the sort order is off AND your summary rows are missing.   The first issue can be resolved by adding a dedicated sort column to the Power Query code.  But the second issue can only be solved by using a separate visual, or by - again - redesigning your data model and separating out the header information into a  parent fact table.

 

lbendlin_2-1648948516591.png

 

 

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.