Column conditional formatting and running Average chart

[Anonymous]

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

[v-luwang-msft · Accepted solution]

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

[Anonymous]

Perfect, thanks for that.

[v-luwang-msft · Accepted solution]

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

[Anonymous · Accepted solution]

@v-luwang-msft Hi Lucien, thanks for that I'll give it a try.

Regards

Fred