Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Color Coding measures in matrix row

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





Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        v-zhangti
        Community Support
        4 years ago

        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.