Forum Discussion

Br1-981's avatar
Br1-981
Helper I
2 years ago
Solved

Custom Matrix

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 ...
"Dashboard Design"
"deneb"
  • giammariam's avatar
    giammariam
    2 years ago

    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.

giammariam's avatar
giammariam
Solution Sage
2 years ago

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's avatar
Br1-981
Helper I
2 years ago

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? 😄