Custom Matrix

[Br1-981]

Hi,

I would build a matrix like this including 2 measures (assume costs and % of each class at category level).

Is there any way to build something like this?

Consider that categories are dynamic while classes are static: 

At the moment I have no idea about how to develop this, problably deneb could be a way...

Thanks in advance

[Br1-981]

Hi,

I've updated the .pbix file so maybe it is more clear 

matrix with images 

(Images area completely random)

Thanks

[giammariam]

Hi @Br1-981, I'm not understanding what you're trying to achieve with the updated design, so it is difficult for me to identify how the measures I created for you would need to be updated. Could you help me understand what you'd like the new matrix to look like? Maybe a picture of the desired outcome would help. If you have an updated .pbix file, that'd help as well. Thanks!

[Br1-981]

thanks a lot, I'll test asap and let you know about it!

In this scenario, do you think it is possible to format (font size and colour) the 2 values as showed in my example?

[giammariam]

Hey @Br1-981, here's what's involved with the dynamic SVG approach for formatting:

Measures:

_Sales = 
VAR _sales = SUMX(
    Sheet1,
    Sheet1[ Sales]
)

RETURN _sales

_Shares = 
VAR _selectedSumOfSales = [_Sales]
VAR _selectedProduct = SELECTEDVALUE(Sheet1[Product])
VAR _totalSalesPerSegment = SUMX(
    FILTER(
        ALLSELECTED(Sheet1),
        Sheet1[Product] = _selectedProduct
    ),
    Sheet1[ Sales]
)
RETURN DIVIDE(_selectedSumOfSales, _totalSalesPerSegment)

_SVG_Sales_&_Shares = 
VAR _imageWidth = 80
VAR _imageHeight = 40
VAR _fontSize = 12
VAR _backgroundHex = "#f2f2f2"
VAR _salesHex = "#023261"

VAR _sales = [_Sales]

/*** 
Dynamic number formatting. Found here:
https://community.fabric.microsoft.com/t5/Custom-Visuals-Ideas/Auto-Format-Numbers-in-Billions-Millions-Thousands-etc/idi-p/1439322
***/
VAR _safeLog =
    IFERROR ( ABS(INT ( LOG ( ABS ( _sales), 1000 ) )), 0 )
VAR dp = 1

VAR _salesFormatted = ROUND ( DIVIDE ( _sales, 1000 ^ _safeLog ), dp )
        & SWITCH ( _safelog, 1, "K", 2, "M", 3, "B", 4, "T" )
/***/

VAR _sharesFormatted = FORMAT([_Shares], "0.00%")

VAR _prefix = "data:image/svg+xml;utf8,"
VAR _svg = "
<svg viewBox='0 0 "& _imageWidth &" "& _imageHeight &"' xmlns='http://www.w3.org/2000/svg'>
  <rect fill='"&_backgroundHex&"' width='"&_imageWidth&"' height='"&_imageHeight&"'></rect>
  <text style='fill: "&_salesHex&"; font-family: Segoe UI; font-size: "&_fontSize&"px; font-weight: 700; white-space: pre; text-anchor: middle; transform-origin: 4.5px 12.957px;' x='"& _imageWidth/2 &"' y='14'>"&COALESCE(_salesFormatted, "")&"</text>
  <text style='fill: "&_salesHex&"; font-family:  Segoe UI; font-size: "&_fontSize&"px; white-space: pre; text-anchor: middle;' x='"& _imageWidth/2 &"' y='30'>"&COALESCE(_sharesFormatted, "")&"</text>
</svg>
"

RETURN _prefix&_svg

Set the Image size desired dimensions under the formatting properties. What ever values you use, you'll want to update the _imageWidth and imageHeight variables in the [_SVG_Sales_&_Shares] measure.

[_SVG_Sales_&_Shares] is the measure to use in the Values well for the matrix.

Here is the result:

[Br1-981]

thanks a lot, it is amazing!

Just one thing, while lookign for an alternative I built the matrix in this way:

putting on rows and colums images that are in dedicated dimensions (IMAGE_URL):

 Using your measurements did not work, should I reference the calculated measures to the IMAGE_URL instead of native dimensions (product and segment) or am I asking too much? 😄

[giammariam]

Hey @Br1-981. Do you mind providing a sanitized example dataset (here's how) that matches the column names that you're wanting to use in your implementation? I'd be happy to try and help.

[Br1-981]

Hi,

here you got a .pbix file made using a sample dataset from PBI

Pbix file 

Thanks in advance for your support!

[giammariam · Accepted solution]

@Br1-981, thanks for the .pbix. I created 2 measures:

_Shares = 
VAR _selectedSumOfSales = SUMX(
    Sheet1,
    Sheet1[ Sales]
)
VAR _selectedProduct = SELECTEDVALUE(Sheet1[Product])
VAR _totalSalesPerSegment = SUMX(
    FILTER(
        ALLSELECTED(Sheet1),
        Sheet1[Product] = _selectedProduct
    ),
    Sheet1[ Sales]
)
RETURN DIVIDE(_selectedSumOfSales, _totalSalesPerSegment)

_Sales&SharesFormatted = 
VAR _lineBreak = UNICHAR(10)
VAR _sales = SUMX(
    Sheet1,
    Sheet1[ Sales]
)

/*** 
Dynamic number formatting. Found here:
https://community.fabric.microsoft.com/t5/Custom-Visuals-Ideas/Auto-Format-Numbers-in-Billions-Millions-Thousands-etc/idi-p/1439322
***/
VAR _safeLog =
    IFERROR ( ABS(INT ( LOG ( ABS ( _sales), 1000 ) )), 0 )
VAR dp = 1

VAR _salesFormatted = ROUND ( DIVIDE ( _sales, 1000 ^ _safeLog ), dp )
        & SWITCH ( _safelog, 1, "K", 2, "M", 3, "B", 4, "T" )
/***/

VAR _sharesFormatted = FORMAT([_Shares], "0.00%")

RETURN _salesFormatted&_lineBreak&_sharesFormatted

I then placed [_Sales&SharesFormatted] as the only field in the Values well. 

You will want to play with the formatting in the [_Sales&SharesFormatted] measure.

If you need the sales in bold, this can be down using dynamic SVGs. I can help with this if needed. Otherwise, if this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.

[Br1-981]

thanks a lot, I'll test asap and let you know about it!

In this scenario, do you think it is possible to format (font size and colour) the 2 values as showed in my example?