cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abukapsoun
Helper V
Helper V

Average & conditional formatting

Hi Gents,

 

Need your expertise please, 

I have the following table

 

CostJanuaryFebruaryMarch....August
ItemX10255....250
ItemY253070....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,

 

1 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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

Hi Greg,

 

In fact thats how exactly the data look like

 

ItemsP01P02P03....P08
item1102010....500
item210155....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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

Thanks you!

 

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
Super User
Super User

@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

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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

 

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors