Forum Discussion
Highlight highest color on column chart using Field Parameters and conditional formatting
- 1 year ago
Hi learner24
You measure will not work because SELECTEDMEASURE not work with field parameter rather it works with calculation group. You can debug by returning simply _CurrentValue and you will see the result is blank.
Also you need to compare current value with the summarized value, other wise you would not be able to identify max value.
So to make it dynamic, you need to repeate same operation for each measure, then use selected value, compare using if or switch condition and return desired result.
Try this below code (I have only 2 measure, since you have 5, then repeate same operation for 5 of them):MaxColumnColor = VAR _selectedMeasure = SELECTEDVALUE(Analysis[Analysis Order]) //For Revenue VAR _ValueTableRev = CALCULATETABLE(ADDCOLUMNS(SUMMARIZE('Calendar', 'Calendar'[Month]), "@Rev", [Revenue]), ALLSELECTED()) VAR _MaxValueRev = MAXX(_ValueTableRev, [@Rev]) VAR _RevCol = SWITCH(TRUE(), [Revenue]=_MaxValueRev, "Green", "Gray") //For Quantity VAR _ValueTableQty = CALCULATETABLE(ADDCOLUMNS(SUMMARIZE('Calendar', 'Calendar'[Month]), "@Qty", [OrderQty]), ALLSELECTED()) VAR _MaxValueQty = MAXX(_ValueTableQty, [@Qty]) VAR _QtyCol = SWITCH(TRUE(), [OrderQty]=_MaxValueQty, "Purple", "Gray") //Result VAR _Result = SWITCH( TRUE(), _selectedMeasure = 0, _QtyCol, _selectedMeasure = 1, _RevCol, "Blue" ) RETURN _ResultI have used Order of field parameter than name. It is easy.
Here is my desired output:
When Order Qty:When Revenue:
Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz
Hi learner24
You measure will not work because SELECTEDMEASURE not work with field parameter rather it works with calculation group. You can debug by returning simply _CurrentValue and you will see the result is blank.
Also you need to compare current value with the summarized value, other wise you would not be able to identify max value.
So to make it dynamic, you need to repeate same operation for each measure, then use selected value, compare using if or switch condition and return desired result.
Try this below code (I have only 2 measure, since you have 5, then repeate same operation for 5 of them):
MaxColumnColor =
VAR _selectedMeasure = SELECTEDVALUE(Analysis[Analysis Order])
//For Revenue
VAR _ValueTableRev = CALCULATETABLE(ADDCOLUMNS(SUMMARIZE('Calendar', 'Calendar'[Month]), "@Rev", [Revenue]), ALLSELECTED())
VAR _MaxValueRev = MAXX(_ValueTableRev, [@Rev])
VAR _RevCol = SWITCH(TRUE(), [Revenue]=_MaxValueRev, "Green", "Gray")
//For Quantity
VAR _ValueTableQty = CALCULATETABLE(ADDCOLUMNS(SUMMARIZE('Calendar', 'Calendar'[Month]), "@Qty", [OrderQty]), ALLSELECTED())
VAR _MaxValueQty = MAXX(_ValueTableQty, [@Qty])
VAR _QtyCol = SWITCH(TRUE(), [OrderQty]=_MaxValueQty, "Purple", "Gray")
//Result
VAR _Result =
SWITCH(
TRUE(),
_selectedMeasure = 0, _QtyCol,
_selectedMeasure = 1, _RevCol,
"Blue"
)
RETURN
_ResultI have used Order of field parameter than name. It is easy.
Here is my desired output:
When Order Qty:
When Revenue:
Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz