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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
RKM
Helper IV
Helper IV

Help Needed Please - KPI indicator At Aggregate Level for each Category

Hi There,

Please can someone help with any visuals guide or sample pbix for me here?

We have 'Program' and under every program, a few Tasks (1 to many). Not always all tasks Or All Programs will execute.

We track durations of each Tasks, (which can be used to add and get duration of different Program) and we also have Threshold (At Program Level).

Users are looking for Power BI chart/Report to See on a given date whether a Program executed, and if yes then whether

  • Is it within Or Equal Thresold limit (then Green) else Red.
  • Is it within Or Equal to the Average of Last 3 Execution duration of the Program (then Green) else Red. To note, this [Last 3 days] is indepent of any filter selection, rather it's purely based on entire dataset and last 3 run of a program is to be considered.

Here is my data sample

Data.JPG

 

 

 

 

 

 

 

 

So we have 3 Tables.. 1 for Mapping between, Program and Task. Another for Program to Threshold Mapping; and finally, Log table for Task to Actual Duration per day.

From Here we can actually try to fist calculate 3days average for PLAN1 & 2, which will be like below,

Average.JPG

 

 

 

 

 

 

 

 

And finally users need something as below (A date Slicer and a chart)....

Example, shows different value selections in date slicer

Chart.JPG

Thanks Much...

6 REPLIES 6
RKM
Helper IV
Helper IV

Hi @TomMartens 

Here is the file attached. Please could check if this would be helpful for further checks. Many Thanks...

Sample PBIX 

Finally, I must also mention that in SQL its quite easy and I can do all calculations there and finally fetch result set but users are really keen to see if this can also be done in Power BI. Are there any visual (may be KPI matrix blah blah type) which can address these please.

 

So, + point for solving in Power BI if you could help. Thanks again

RKM
Helper IV
Helper IV

I tried but couldn't. Apologies!

RKM
Helper IV
Helper IV

Unfortunately I can not upload any file to any external places like, one drive etc, but,

I have given every datapoint details on my email. Only 3 tables as below, straight forward Relationship (pic below)

 

Data.JPG

datamodel.JPG

Hey @RKM ,

as I mentioned I need some data to experiment with, before I'm able to provide a solution. I'm not willing to spent time on typing the data from your screenshots into a pbix.

 

Maybe you will try to use the table feature

TomMartens_0-1615717539200.png

or copy the sample as text into a thread.

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

Hey @RKM ,

 

thanks for the concise information, as most of the time answering questions also needs some experimentation, please provide sample data.

Create a pbix that contains sample data but still reflects your data model, upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data (instead of using the enter data feature in Power BI Desktop), share the xlsx as well.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.