Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
I am trying to add some conditional formatting to a matrix table but struggling to get the correct result.
I am trying to make the value in the table turn either Green or Red. Green if the value in the cell is greater than the Average of the row total and Red if it is below. Screenshot attached with the result i am after.
i.e if the total is £155,750 and there are 4 entries then £155,750/4 - £38,937.5 Any values greater than this in that row can be Green. Obviously each row has different averages.
I have created a measure to calculate the average per row-
Solved! Go to Solution.
@JamesGordon Have you checked that calculation in context of the individual cells? Seems like you would need something like:
myAverage = divide( CALCULATE(sum(Sheet[Total Sell]),ALLEXCEPT(Sheeet[Make])),CALCULATE(COUNTROWS(DISTINCT(Sheet[Rep])),ALLEXCEPT(Sheet[Make])))
Hi @JamesGordon,
Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@JamesGordon Have you checked that calculation in context of the individual cells? Seems like you would need something like:
myAverage = divide( CALCULATE(sum(Sheet[Total Sell]),ALLEXCEPT(Sheeet[Make])),CALCULATE(COUNTROWS(DISTINCT(Sheet[Rep])),ALLEXCEPT(Sheet[Make])))
Thank you, thats appears to give me the average of the row total so getting nearer. But how can i now make that figure colour coordinate the individial cells?
Try coloring based on a measure like
SUM ( Sheet[Total Sell] ) - [myAverage]
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |