Forum Discussion
How to create a simple Bowler chart in Power BI?
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.
8 Replies
- bhanu_gautam
Super User
Deepak89028 , You can use Matrix for such view and use conditional formatting for color changes
Thank you bhanu_gautam , I did use the matrix to get the desired output. Any hints or guidance for the color coding?
- bhanu_gautam
- MasonMA
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:)
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
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:)
- 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.
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.
