Average & conditional formatting

[abukapsoun]

Hi Gents,

Need your expertise please, 

I have the following table

Cost	January	February	March	..	..	August
ItemX	10	25	5	..	..	250
ItemY	25	30	70	..	..	30

I get this table periodically at each month. At each month, taking the example of August I would like to check the average of all previous periods and if the cost of August is higher than the average, then i highlight the cell with a color, for example red or something. so it means cost of ItemX should be highlight in Red, and itemY still within average. 

Would that be possible? If not, with conditional formatting at least with something that tell cost of itemX in August is above average.

Thanks,

[Greg_Deckler]

@abukapsoun Is that your actual source data? Do you have an actual date column to work with? If that is your actual data as it comes in from the source you will want to right-click your first column and choose Unpivot outher columns. Then you are going to need a table in your data model like:

Month	Rank
January	1
February	2
March	3

Anyway, I hate to go into detail until I know about an actual date column or not.

[abukapsoun]

Hi Greg,

In fact thats how exactly the data look like

Items	P01	P02	P03	..	..	P08
item1	10	20	10	..	..	500
item2	10	15	5	..	..	10
.
.
.

the P01, P02.. represent periods, like months.

Every month I get an updated period that I add to the table. So if we say we are now September, i would want to analyse the figures of August. 

I would want to highlight the items that are in August having value, higher than the average of the whole period. 

So in September I come and look at the values of P08. If P08 value of Item1 is higher than the average of all previous periods (P1 -> P7) then I highlight the value in red. if not, we leave it.

I would need to repeat the same on every period, so if we are next in October, I would want to analyse the values of P09 while considering the average (P1-> P8). 

I hope I could make it clear enough.

Thanks a lot in advance,

[Greg_Deckler · Accepted solution]

@abukapsoun So, I would go into Power Query, select your Items column, right-click and choose Unpivot other columns. That should make everything you are trying to do much, much easier. @ me if you need me.

[abukapsoun]

Thanks you!

[abukapsoun]

Thanks. Done that, but then what? 

Would I be able to apply the conditional formatting to the column values if it is above the average? 

[amitchandak]

@abukapsoun ,

If you are getting data only monthly and you can have date or rank on month

Cumm Sales = CALCULATE(Average(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))

Cumulative a month before. If you getting daily data then sum the data and divide by distinct month

Use the date table

[abukapsoun]

Thanks for your reply, but I didnt really get the formula. 

Is that a measure? moreover I can see in the formula [Sales Amount] column, to which column exactly in my table that refers to? I dont have such column

can we do it without using the date? because in fact i dont have them as months but instead P01, P02, P03, instead of January, Feb, March..

Thanks again