Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have a requirement where i need to show alternating colour in bar graph which has only single measure (salesamount) in Values and the AXIS(Customer) around 5000 Customer.
i created a rank on customer based on sales amount,
Rankcolumn=rankx(allselected(customer[customername]),sales[Salesamount],,,Dense)
Used this measure as below to calculate conditional formatting field .
color format field=
var -cl=mod([Rankcolumn],2)
return
if(cl=1,"colour1","colour2")
I used this color format field measure in data colours -- format by-- based on field
this is working perfectly untill there are ties in rank, if there is tie then bar shows same colour for that ranks.
is there any work around for this?
Thanks in advance,
Suyog
Solved! Go to Solution.
@suyogbi
Since you have ties in your ranking, the Rank calculation has to break it first. I created the following ranking measure and used it in your CF measure. The file is attached below my signature.
Rank =
VAR __customernamerank =
ADDCOLUMNS (
ALLSELECTED ( Customers[Customer Name] ),
"RankCust",
RANKX (
ALLSELECTED ( Customers[Customer Name]),
CALCULATE ( MAX ( Customers[Customer Name] ) )
)
)
VAR __maxcust =
MAXX ( __customernamerank, [RankCust] )
VAR __customerrank =
ADDCOLUMNS (
__customernamerank,
"Rank",
RANKX ( __customernamerank, [Sales Amount] * __maxcust + [RankCust] )
)
RETURN
IF(
HASONEVALUE(Customers[Customer Name]),
MAXX(FILTER(__customerrank,Customers[Customer Name] = MAX(Customers[Customer Name])),[Rank])
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@suyogbi
Since you have ties in your ranking, the Rank calculation has to break it first. I created the following ranking measure and used it in your CF measure. The file is attached below my signature.
Rank =
VAR __customernamerank =
ADDCOLUMNS (
ALLSELECTED ( Customers[Customer Name] ),
"RankCust",
RANKX (
ALLSELECTED ( Customers[Customer Name]),
CALCULATE ( MAX ( Customers[Customer Name] ) )
)
)
VAR __maxcust =
MAXX ( __customernamerank, [RankCust] )
VAR __customerrank =
ADDCOLUMNS (
__customernamerank,
"Rank",
RANKX ( __customernamerank, [Sales Amount] * __maxcust + [RankCust] )
)
RETURN
IF(
HASONEVALUE(Customers[Customer Name]),
MAXX(FILTER(__customerrank,Customers[Customer Name] = MAX(Customers[Customer Name])),[Rank])
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
98 | |
90 | |
82 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |