Forum Discussion
Column conditional formatting and running Average chart
I am trying to create a column and line chart using data from MS Project, pulled into Excel (because I couldn't work out how to calculate the "running" average of the RAGs up to "this" week - easy in Excel, used SUMIF divided by COUNTIF) and then picked up in Power BI.
The columns are designed to show the RAG by week, over the financial year for 50 (or so) different projects, using a slicer to show an individual project’s “progress”.
The line will show the average value to date
Where I’m struggling is with conditional formatting, dependant upon the RAG of the weekly column, 1 to show green, 2 – amber, 3 – red) Each project will have a single entry per week, 1, 2 or 3 and I’m trying to get the columns to show the appropriately coloured column for the RAG for each week, as per the manually created Excel chart below.
Week Ending | OverallRAG | Project Name | Average |
01-Apr | 1 | My Project 1 | 1 |
08-Apr | 1 | My Project 1 | 1 |
14-Apr | 2 | My Project 1 | 1.333333 |
22-Apr | 2 | My Project 1 | 1.5 |
29-Apr | 2 | My Project 1 | 1.6 |
06-May | 3 | My Project 1 | 1.833333 |
13-May | 2 | My Project 1 | 1.857143 |
20-May | 2 | My Project 1 | 1.875 |
27-May | 1 | My Project 1 | 1.777778 |
03-Jun | 1 | My Project 1 | 1.7 |
10-Jun | 1 | My Project 1 | 1.636364 |
17-Jun | 1 | My Project 1 | 1.583333 |
24-Jun | 1 | My Project 1 | 1.538462 |
01-Jul | 1 | My Project 1 | 1.5 |
08-Jul | 1 | My Project 1 | 1.466667 |
15-Jul | 1 | My Project 1 | 1.4375 |
22-Jul | 1 | My Project 1 | 1.411765 |
29-Jul | 1 | My Project 1 | 1.388889 |
05-Aug | 1 | My Project 1 | 1.368421 |
12-Aug | 1 | My Project 1 | 1.35 |
19-Aug | 1 | My Project 1 | 1.333333 |
26-Aug | 1 | My Project 1 | 1.318182 |
09-Sep | 1 | My Project 1 | 1.304348 |
16-Sep | 1 | My Project 1 | 1.291667 |
23-Sep | 1 | My Project 1 | 1.28 |
30-Sep | 1 | My Project 1 | 1.269231 |
07-Oct | 1 | My Project 1 | 1.259259 |
14-Oct | 2 | My Project 1 | 1.285714 |
21-Oct | 2 | My Project 1 | 1.310345 |
28-Oct | 3 | My Project 1 | 1.366667 |
11-Nov | 2 | My Project 1 | 1.387097 |
18-Nov | 1 | My Project 1 | 1.375 |
25-Nov | 1 | My Project 1 | 1.363636 |
HI Anonymous ,
Pls test the below measure:
AV1 = CALCULATE(SUM('Table'[Average])/COUNT('Table'[RAG]),FILTER(ALL('Table'),'Table'[Weekend]<=MAX('Table'[Weekend])))Best Regards
Lucien
3 Replies
HI Anonymous ,
Pls test the below measure:
AV1 = CALCULATE(SUM('Table'[Average])/COUNT('Table'[RAG]),FILTER(ALL('Table'),'Table'[Weekend]<=MAX('Table'[Weekend])))Best Regards
Lucien