cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
suyogbi
Frequent Visitor

Alternating colours for BAR Graph with single measure

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

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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])
)

 

Fowmy_0-1628627006461.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@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])
)

 

Fowmy_0-1628627006461.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

suyogbi
Frequent Visitor

@Fowmy 

Thank you for reply👍. your solution fulfilled my requirement.😊😊

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors