Hi,I am new to power bi , I want to compare columns with adjacent ones in Matrix Visualisation based on the BizDate value filters and then mark them with respective colour code on their comparison.Please consider below table as input:FilterBizDateBizCountA20210131Prime100B20210131Prime101C20210131Prime102D20210131Prime103A20210228Prime105B20210228Prime90C20210228Prime91D20210228Prime92E20210331Prime93A20210331Prime105B20210331Prime80C20210331Prime70D20210331Prime100E20210131Prime90A20210131Growth111B20210131Growth102C20210131Growth103D20210228Growth104E20210228Growth105A20210228Growth100B20210228Growth91C20210228Growth92D20210331Growth93E20210331Growth500A20210331Growth89B20210331Growth90C20210331Growth88D20210331Growth1E20210331Growth2A20210431Growth10B20210431Growth20C20210431Growth30D20210431Growth40E20210431Growth50A20210431Prime50B20210431Prime40C20210431Prime30D20210431Prime20E20210431Prime10 Expected O/PFor Example:1. for selected three dates values - I need to compare 20210331(March) Prime values with 20210228 (Feb) Prime valuesand base on min and max value on comaprsion i will colour code them.Same Comparison on Respective Growth ValuesMin value - Red ColourMax Value - Green Colour2.Now to compare other two adjacent dates 20210228 (Feb) and 20210131 (Jan) and same colour code i will use to compare the min - max value.Similarly for N th date i will compare it with N-1th Date (adjacent) Filter202101312021022820210331 20210431 GrowthPrimeGrowthPrimeGrowthPrimeGrowthPrimeA111100100105891051050B102101919090802040C103102929188703030D104103939211004020E105 500932905010 amitchandak selimovdAnonymouscollinq@wynhopkins@Jihwan_Kim@dm-p

Anonymous

Not applicable

5 years ago

Solved

Conditional Formating of Matrix visualization based on slicer ( slected filter)

Hi,
I am new to power bi , I want to compare columns with adjacent ones in Matrix Visualisation based on the BizDate value filters and then mark them with respective colour code on their comparison.

Please consider below table as input:

Filter	BizDate	Biz	Count
A	20210131	Prime	100
B	20210131	Prime	101
C	20210131	Prime	102
D	20210131	Prime	103
A	20210228	Prime	105
B	20210228	Prime	90
C	20210228	Prime	91
D	20210228	Prime	92
E	20210331	Prime	93
A	20210331	Prime	105
B	20210331	Prime	80
C	20210331	Prime	70
D	20210331	Prime	100
E	20210131	Prime	90
A	20210131	Growth	111
B	20210131	Growth	102
C	20210131	Growth	103
D	20210228	Growth	104
E	20210228	Growth	105
A	20210228	Growth	100
B	20210228	Growth	91
C	20210228	Growth	92
D	20210331	Growth	93
E	20210331	Growth	500
A	20210331	Growth	89
B	20210331	Growth	90
C	20210331	Growth	88
D	20210331	Growth	1
E	20210331	Growth	2
A	20210431	Growth	10
B	20210431	Growth	20
C	20210431	Growth	30
D	20210431	Growth	40
E	20210431	Growth	50
A	20210431	Prime	50
B	20210431	Prime	40
C	20210431	Prime	30
D	20210431	Prime	20
E	20210431	Prime	10

Expected O/P

For Example:
1. for selected three dates values - I need to compare 20210331(March) Prime values with 20210228 (Feb) Prime values
and base on min and max value on comaprsion i will colour code them.

Same Comparison on Respective Growth Values

Min value - Red Colour

Max Value - Green Colour

2.Now to compare other two adjacent dates 20210228 (Feb) and 20210131 (Jan) and same colour code i will use to compare the min - max value.
Similarly for N th date i will compare it with N-1th Date (adjacent)

Filter	20210131	20210228	20210331		20210431
	Growth	Prime	Growth	Prime	Growth	Prime	Growth	Prime
A	111	100	100	105	89	105	10	50
B	102	101	91	90	90	80	20	40
C	103	102	92	91	88	70	30	30
D	104	103	93	92	1	100	40	20
E	105		500	93	2	90	50	10

amitchandak

selimovd

Anonymous

collinq

@wynhopkins

@Jihwan_Kim

@dm-p

Hi Anonymous ,

Has modified the date value you have provided and you can create these measures:

previous = 
CALCULATE (
    SUM ( 'Table'[Count] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Filter], 'Table'[Biz] ),
        [BizDate]
            = CALCULATE (
                MAX ( 'Table'[BizDate] ),
                FILTER ( ALL ( 'Table' ), [BizDate] < MAX ( 'Table'[BizDate] ) )
            )
    )
)

format = 
IF (
    SELECTEDVALUE ( 'Table'[BizDate] )
        = CALCULATE ( MIN ( 'Table'[BizDate] ), ALL ( 'Table' ) ),
    "black",
    IF ( SUM ( 'Table'[Count] ) >= [previous], "green", "red" )
)

Set conditional format for the [Count] field:

Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

test.pbix62 KB

3 Replies

amitchandak
Super User
5 years ago
Anonymous , how you plan to select. if you plan to use time intelligence

we can create measures like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

diff = [MTD Sales]-[last MTD Sales]

and then create a color measure ,

if([diff] >0, "green", "red")

and use that in conditional formatting using "field value" option

refer my video: https://www.youtube.com/watch?v=RqBb5eBf_I4
- Anonymous
  Not applicable
  5 years ago
  amitchandak we are not using time intelligence exaclty, however we are using slicer to select the Dates, which exists in source only.
  Sample report:

v-yingjl

Community Support

5 years ago

Hi Anonymous ,

Has modified the date value you have provided and you can create these measures:

previous = 
CALCULATE (
    SUM ( 'Table'[Count] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Filter], 'Table'[Biz] ),
        [BizDate]
            = CALCULATE (
                MAX ( 'Table'[BizDate] ),
                FILTER ( ALL ( 'Table' ), [BizDate] < MAX ( 'Table'[BizDate] ) )
            )
    )
)

format = 
IF (
    SELECTEDVALUE ( 'Table'[BizDate] )
        = CALCULATE ( MIN ( 'Table'[BizDate] ), ALL ( 'Table' ) ),
    "black",
    IF ( SUM ( 'Table'[Count] ) >= [previous], "green", "red" )
)

Set conditional format for the [Count] field:

Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

test.pbix62 KB

Forum Discussion

Conditional Formating of Matrix visualization based on slicer ( slected filter)

3 Replies

Related content

Conditional Formatting using DAX

Conditional Formatting

Conditional Formatting

Matrix Row Conditional Formatting

Condition formatting