Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 exampleHeader 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. 

pbi_example2.PNG

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

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.