topic Re: Custom Matrix in Custom Visuals Development Discussion

Custom Matrix

Br1-981 — Thu, 12 Oct 2023 13:43:59 GMT

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

Re: Custom Matrix

giammariam — Fri, 13 Oct 2023 17:44:22 GMT

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.

Re: Custom Matrix

Br1-981 — Mon, 16 Oct 2023 09:18:14 GMT

Hi,

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

Pbix file

Thanks in advance for your support!

Re: Custom Matrix

giammariam — Mon, 16 Oct 2023 13:49:03 GMT

@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.

Re: Custom Matrix

Br1-981 — Mon, 16 Oct 2023 13:49:43 GMT

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?

Re: Custom Matrix

Br1-981 — Mon, 16 Oct 2023 14:33:33 GMT

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?

Re: Custom Matrix

giammariam — Mon, 16 Oct 2023 16:14:30 GMT

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&colon;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:

Re: Custom Matrix

Br1-981 — Tue, 17 Oct 2023 08:53:24 GMT

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

Re: Custom Matrix

giammariam — Tue, 17 Oct 2023 11:40:37 GMT

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!

Re: Custom Matrix

Br1-981 — Tue, 17 Oct 2023 13:26:08 GMT

Hi,

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

matrix with images

(Images area completely random)

Thanks