Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculating and visualizing fluctuating benchmarks in bar charts

Hi,

 

 

I am trying to visualize the performance of a cleaning process that occurs at 85 different objects. The main goal is showing the performance of the process in a particular year, month, week and on case level which I am able to show with slicers. However, I would like to add a feature that shows a benchmark of the 10 'fastest' cases for each object.

 

Only cases that are 'complete' should be taken in consideration for this benchmark. A process instance is completed whenever the particular process contains a [Start_process] and [End_process] record and the time to complete takes 90 minutes or more.

 

To do this I have a header and detail table available. The tables are connected to each other by an unique BATCH_ID. The picture below shows an example of the header- and detail table. In the example below the only case that should be taken in consideration for the benchmark is the case with BATCH_ID 3, because a) it contains a [Start_process] and [End_process] record and b) its duration is longer than 90 minutes. Allthough the incomplete cases should not be used for the benchmark, they should be shown in a visualization that shows all cases of the selected month. Header and detail table example

In an ideal scenario the solution would look something like the picture below with a changing benchmark whenever the selected object changes. 

As I am new to Power BI I am not quite sure whether the solution lies in a measure, smart use of DAX, additional slicers or something completely different. 

 

In short, the main challenges are the following bullet points (and combining them in a proper solution!): 

  • Selecting the right cases (containing a Start/end record and with a duration that is 90 minutes or more)
  • Calculating a benchmark for each object
  • Adding this benchmark to an existing visualization 

 

 

Thanks in advance, 

 

Davo

Need Help

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    HI, Anonymous 

    You may try to add a calculate column in header table by this formula

    Column = IF(CALCULATE(COUNTA(detail[Object]),FILTER(RELATEDTABLE(detail),detail[Object]=Header[Object]&&detail[Process_step]="End_process"))>=1&&Header[Total_Time]>=90,Header[Total_Time])

     then use this column as a benchmark.

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi, v-lili6-msft 

       

      Thanks for your reply and the advice! I am having some trouble while implementing your solution. Whenever I try to create the calculated column as you suggested I get the following error:

       

      "DAX comparisons operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values." 

       

      I do not entirely understand this warning. The outcome of the calculated column should be an average for each object, if I understand your solution correctly. I dont see where there would be a comparison between a boolean and text. 

       

      Could u help me debug this error?

       

      Kind Regards,

       

      Davo