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

[RKM]

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

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,

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

Example, shows different value selections in date slicer

Thanks Much...

[RKM]

Hi @TomMartens 

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

Sample PBIX 

[RKM]

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]

I tried but couldn't. Apologies!

[RKM]

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)

[TomMartens]

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

or copy the sample as text into a thread.

Regards,

Tom

[TomMartens]

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