Forum Discussion
Average & conditional formatting
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,
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.
7 Replies
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
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
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.
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,
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.
