cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
areismarc
New Member

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

 

0 REPLIES 0

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates