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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX color scaling into matrix

Hello team,

I am new in the pbi journey and really appreciate any help with DAX expression to help with color scaling into Matrix table.

We have exchanged some ideas with different group and users, and they proposed a great solution but we are still need support to understand, or maybe receive a different solution to move forward.

The expected color scaling in the matrix would be:

--> Based on all values per individual month row and comparing with all month columns, the highest number should be green, middle as yellow and lowest as red. And the same behavior is expected in the next row level separately. But per below reference with arrows in blue, some example values are not matching with expected color.

areismarc_0-1624475851262.png

 

Could someone help please with deep investigation into DAX expression below? Or any different proposal is super welcome as well.

 

Indicator =
VAR _currentcolumn =
SELECTEDVALUE ( 'Sample'[MONTH_ORDER])
VAR _currentrow =
SELECTEDVALUE ( 'Sample'[Joining_Order] )
-------------------------------
VAR _proportion =
IF (
_currentrow > _currentcolumn,
BLANK (),
--run this only in the relevant cells
VAR _maxcolumnmonth =
CALCULATE (
MAX ( 'Sample'[MONTH_ORDER]),
ALLSELECTED ( 'Sample'[MONTH_ORDER]),
REMOVEFILTERS ( 'Sample'[Joining_Order])
) --get the max value ignoring filters from the matrix
VAR _mincolumnmonth =
CALCULATE (
MIN ( 'Sample'[MONTH_ORDER]),
ALLSELECTED ( 'Sample'[MONTH_ORDER]),
REMOVEFILTERS ( 'Sample'[Joining_Order])
) --get the min value ignoring filters from the matrix
--------------------------------------
VAR __rlvntcolumntable =
CALCULATETABLE (
VALUES ( 'Sample'[MONTH_ORDER]),
ALL ( 'Sample'[MONTH_ORDER], 'Sample'[Joining_Order]),
'Sample'[MONTH_ORDER] >= _mincolumnmonth
&& 'Sample'[MONTH_ORDER] <= _maxcolumnmonth
) -- --build a table with all the start month values in the slicer selection ignoring the filter from the current month column in the matrix
VAR __rlvntcolumntable2 =
SELECTCOLUMNS (
__rlvntcolumntable,
"@currentmonth", 'Sample'[MONTH_ORDER] & ""
) --remove the lineage to the Planilha table
VAR __rlvntcolumntable3 =
ADDCOLUMNS (
__rlvntcolumntable2,
"@qty",
VAR _currentcolumnmonth =
VALUE ( [@currentmonth] )
RETURN
CALCULATE (
MAX ( 'Sample'[Qty] ),
ALL ( 'Sample'[MONTH_ORDER], 'Sample'[Joining_Order] ),
'Sample'[MONTH_ORDER] = _currentcolumnmonth,
'Sample'[Joining_Order] = _currentrow
)
) --create a temp table with the Qty for all the columns in the current row
VAR __rlvntcolumntable4 =
FILTER ( __rlvntcolumntable3, [@qty] <> BLANK () ) --filter out rows which has blank customer counts (rows where row month < current column month)
--------------------------------------
--calculation of the proportion
VAR _stddeviation =
STDEVX.P ( __rlvntcolumntable4, [@qty] )
VAR _maxqty =
MAXX ( __rlvntcolumntable4, [@qty] )
VAR _minqty =
MINX ( __rlvntcolumntable4, [@qty] )
VAR _currentcohortvalue =
SELECTEDVALUE ( 'Sample'[Qty])
VAR _currentproportion =
SWITCH (
TRUE (),
_currentcohortvalue = _maxqty, 1,
_currentcohortvalue = _minqty, 0,
ABS ( 1 - DIVIDE ( _stddeviation, _currentcohortvalue, BLANK () ) )
) --absolute value
RETURN
_currentproportion
)
RETURN
_proportion

 

1 REPLY 1
lbendlin
Super User
Super User

@Anonymous Please provide sample data in usable format (not as a picture - maybe insert into a table?) .

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors