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