How to create a simple Bowler chart in Power BI?

[Deepak89028]

Hi Team,

I am new to PowerBI. I need to create  a Bowler chart.

Here is sample data.

Metric	Month	Plan	Actual
Fulfillment	1	85.0	84.0
Fulfillment	2	86.0	86.0
Fulfillment	3	87.0	88.0
Fulfillment	4	88.0	89.0
Fulfillment	5	89.0	89.0
Fulfillment	6	90.0	91.0
Fulfillment	7	91.0	90.0
Fulfillment	8	92.0	92.0
Fulfillment	9	93.0	92.0
Fulfillment	10	94.0	94.0
Fulfillment	11	95.0	95.0
Fulfillment	12	96.0	96.0

The output Bowler that I am looking for should look similar to the one shown below.

Please note that I might have multiple metric.

Any help is appreciated.

Thanks in advance.

[MasonMA · Accepted solution]

Hi @Deepak89028 

It would be DAX measure like below. 

HighlightActualRow = 
IF (
    SELECTEDVALUE('Table'[Attribute]) = "Actual",
    "Red",  
    BLANK()    
)

 Apply on Values, 

and set up as in picture 

You would have 

Hope it helps:) 

[Deepak89028 · Accepted solution]

Thank you, @MasonMA and @bhanu_gautam , for your time and suggestions.

The final solution is extended basis suggestion from @MasonMA.

Firstly, I need to unpivot the columns of plan and actual and load the values in a matrix, wherein we need to load Attribute as Rows and Date as Column. 

I need to create a dax measure that gives output by comparing Plan and Actual in terms of 1 and 0 for respective month. Refer the DAX measure below. 

Color Logic = 
VAR CurrentAttribute = SELECTEDVALUE('KPI'[Attribute])
VAR CurrentMonth = SELECTEDVALUE('KPI'[Month])
VAR ActualValue = 
    CALCULATE(
        MAX('KPI'[Value]),
        'KPI'[Attribute] = "Actual",
        'KPI'[Month] = CurrentMonth
    )
VAR PlanValue = 
    CALCULATE(
        MAX('KPI'[Value]),
        'KPI'[Attribute] = "Plan",
        'KPI'[Month] = CurrentMonth
    )
RETURN 
    IF(
        CurrentAttribute = "Actual",
        IF(ActualValue > PlanValue, 1,IF(ActualValue = PlanValue, 1, 0)),
        BLANK()
    )

Then in conditional formatting, use the "Rules" format style and select the newly created measure to define the color as shown below.

Here is the final output.

[rajendraongole1]

Hi @Deepak89028  - can you try Line and Clustered Column Chart.

on bars (columns) show actuals

and in line add plan

place the axis with month and then apply conditional formatting with color calculated column as like eg:

ColorCode =
IF([Actual] < [Plan], "Red", "Green")

Hope this helps.

[MasonMA]

@Deepak89028 

Hi, before using Matrix visual in reporting you would need to transform data a bit in Power Query

1. Add a Custom column 

#date(2023, [Month], 1)

2. Unpivot Columns 'Plan' and 'Actual' as below 

to give you

In Power BI reporting, use Attribute as Rows and Date as Columns. and apply your conditional formatting if you like.

Hope it helps:) 

[Deepak89028]

Thank you @MasonMA , it helped.

Can you help me figure out how to do conditional formatting for the color? Since Actual and Plan are unpivoted, I cannot calculate the difference between Plan and Actual and use that to color code. I guess some complex DAX is required here to color code.

[MasonMA · Accepted solution]

Hi @Deepak89028 

It would be DAX measure like below. 

HighlightActualRow = 
IF (
    SELECTEDVALUE('Table'[Attribute]) = "Actual",
    "Red",  
    BLANK()    
)

 Apply on Values, 

and set up as in picture 

You would have 

Hope it helps:) 

[bhanu_gautam]

@Deepak89028 , You can use Matrix for such view and use conditional formatting for color changes

[Deepak89028]

Thank you @bhanu_gautam , I did use the matrix to get the desired output. Any hints or guidance for the color coding? 

[bhanu_gautam]

@Deepak89028 , Go through 

https://www.youtube.com/watch?v=m0h3Ghl6mgY