Color Coding measures in matrix row

[Anonymous]

Hi guys,

I need help with color coding my measure. So far i have tried maxx function and it doesn't seem to work.

My measure below:

ModifiedRevenue = 
//Determine the quarter week no
VAR TDDateWeekNo=[TodaysDate]
Var rowfiltercontext=max(Calendar_Data[Week No])


Var CurrentRevenue=
CALCULATE(
sum(Data[Revenue (Base)]),
FILTER(Data,Data[Category] <>"Won"),
FILTER(Data,Data[Category]<> "Lost"),
FILTER(Calendar_Data,Calendar_Data[Week No]>=TDDateWeekNo))



var lastrevenue= ""
var flag=TDDateWeekNo<=rowfiltercontext
return
if(flag,CurrentRevenue,lastrevenue) 

The matrix visual that is produces. The measure hides all the previous weeks sales data and only shows the sales in Week 12 and Week 13. I would like to highlight the max (green) and min (red) amount for each category. 

End results are as such:

Best Case Min $24,975 (Red) , Max $110,976 (Green)

Pipeline Min $ 12,008 (Red), Max $45,080 (Green)


Tqvm

[amitchandak]

@Anonymous , Create a measure like

Switch(True(),
[Best Case] = calculate(maxx(summarize(Table, Table[Category],Table[Month], "_1" [Best Case]),[_1]), allselected() ), "Green",
[Best Case] = calculate(Minx(summarize(Table, Table[Category],Table[Month], "_1" [Best Case]),[_1]), allselected() ), "Red",
"white"
)

Use the same in conditional formatting using field value option

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[Anonymous]

Hi Amit

Thanks for the reply. I understand that from that measure that you wrote it is by creating a custom table and then finding the min and max. 

Is there a way to do it with a measure ? (Eg: Using the modified revenue measure above) Instead of a table?

Thank you

[v-zhangti]

Hi, @Anonymous 

Can you provide source data for the modified revenue measure for testing? It can be in excel format or a simple PBIX file with sensitive information removed in advance.

Looking forward to your reply.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-zhangti  , 

Thank you for replying . Here is the link for the PBI file 

https://drive.google.com/file/d/1al_C1XXdaokTQqqdy_dJJTSzK_B7nrHJ/view?usp=sharing

Thank you

[v-zhangti]

Hi, @Anonymous 

We can't install non-Microsoft licensed apps on our office computers, so they can't be unzipped. Can you pass the PBIX file separately, please.

Best Regards

[Anonymous]

https://drive.google.com/file/d/10w5Y61OedGThyufTSAxNTCz3MDPqDQeh/view?usp=sharing

please try this 

Thank you

[v-zhangti]

Hi, @Anonymous 

I have tried to test it, Measure cannot output two results at the same time, such as sum value and color. So it is not possible to make changes in Measure ModifiedRevenue to achieve your needs at the same time.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.