Forum Discussion

crainaud's avatar
crainaud
Frequent Visitor
4 years ago
Solved

KPI Tabular Editor color switch based on product

Hi,

I have set up my KPIs using Tabular Editor.  Generally an increase over target is a positive or "green" but I have one item where it should be "red".  

In the example below, I've actually used two matrix tables so it "appears" correctly on my screen, but I'd like to have the same results in one matrix table as this method doesn't always appear correctly on other users screens.

Looking for ideas on how this could be achieved.  All help is greatly appreciated.  Thanks.

Carolyn

 

 

 

Need Help
  • crainaud's avatar
    crainaud
    4 years ago

    Hi -

    Happy to report by studying an Enterprise DNA sample file, I figured out a switch measure that works for this situation.  The variance calculation is based on the specific metric that appears in the table visual.

     
    MTD Var by Metric =
    VAR CurrentItem = SELECTEDVALUE(Metrics[Metric] )

    RETURN
    SWITCH( TRUE(),
    CurrentItem = "Net MWh", [MTD Var],
    CurrentItem = "A Steam Klbs", [MTD Reverse Var],
    CurrentItem = "B Steam Klbs", [MTD Var])
    By using two difference variance measures; MTD Reverse Var (target - actual) and MTD Var (actual - target) I was able to create the Tabular Editor KPI to display the correct colors using the final measure MTD Var by Measure.

    Yalan Wu, thanks for the follow-up!

5 Replies

    • crainaud's avatar
      crainaud
      Frequent Visitor
      4 years ago

      Thanks for getting back so quickly! 

      I have used conditional formatting in the past, but was impressed with using Tabular Editor (TE) to create them.  Saves all that formatting.

      I'm new to TE and had hoped to be able to find how to edit the KPI measures to switch the colors based on the product type.

       

       

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, crainaud ;

    Can you share simple files after removing sensitive information? In addition, the desired output result is attached to solve this problem in more detail. The general idea is to use measure to make a conditional format. 

    Looking forward to your reply！

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • crainaud's avatar
      crainaud
      Frequent Visitor
      4 years ago

      Hi -

      Happy to report by studying an Enterprise DNA sample file, I figured out a switch measure that works for this situation.  The variance calculation is based on the specific metric that appears in the table visual.

       
      MTD Var by Metric =
      VAR CurrentItem = SELECTEDVALUE(Metrics[Metric] )

      RETURN
      SWITCH( TRUE(),
      CurrentItem = "Net MWh", [MTD Var],
      CurrentItem = "A Steam Klbs", [MTD Reverse Var],
      CurrentItem = "B Steam Klbs", [MTD Var])
      By using two difference variance measures; MTD Reverse Var (target - actual) and MTD Var (actual - target) I was able to create the Tabular Editor KPI to display the correct colors using the final measure MTD Var by Measure.

      Yalan Wu, thanks for the follow-up!

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, crainaud ;

    Congratulations on finding a solution. Would you mind accept your replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

     

    Best Regards,
    Community Support Team_ Yalan Wu