Forum Discussion
Iterate automatically trough columns (conditional formatting on matrix table)
Hi,
This is the table created:
I'm using the following measure to do conditional formatting:
color =
var _a=CALCULATE(SUM('Table'[Oct'23]),FILTER(ALL('Table'),'Table'[KPI Name]=MAX('Table'[KPI Name]) && 'Table'[Version]="A"))
var _b=CALCULATE(SUM('Table'[Oct'23]),FILTER(ALL('Table'),'Table'[KPI Name]=MAX('Table'[KPI Name]) && 'Table'[Version]="B"))
return IF(_a>= _b,"#1AAB40", "#D64554")
Any tip is welcome.
Fernando
4 Replies
hi ferk_23 ,
The point is to unpivot your data like:
KPI Name, Version, Month, Value
then everything becomes natural like:
color =
var _a=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[KPI Name]=MAX('Table'[KPI Name]) && 'Table'[Version]="A"))
var _b=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[KPI Name]=MAX('Table'[KPI Name]) && 'Table'[Version]="B"))
return IF(_a>= _b,"#1AAB40", "#D64554")About Unpivot:
https://learn.microsoft.com/en-us/power-query/unpivot-column
Thank for your help.
The columns are now unpivot columns, you know how can I filter to the last month?
Hi ferk_23 ,
The following link can solve your problem; instead of using hard coded "month name" in your sum formula; you should use previous month total sum logic.
I hope it helps you
Any more suggestions? I haven't managed it yet because I have the months as columns
