Multiple group by/ranking issue

[Anonymous]

I have a faily simple data issue, which seems like it could be complicated in Power BI, but I'm a relative newbie so I'm hoping someone can point me in the right direction.

Here's the overview: I have data which includes multiple plants, each plant has multiple wind turbines, and each turbine has multiple alarms which go off on a daily basis. What I need to do is this:

1. Count how many alarms went off per plant per day, by alarm type.

2. Find the top n (in this simple case n = 2) alarms, by plant, for any date range the user selects.

3. Once we know which were the top 2 alarms for each plant, we need to then figure out which turbine(s) had the most instances of that alarm go off in the same time period. 

4. The final output is basically Plant Name, Alarm Code, # of alarms, and Turbine(s) most impacted. That would look like this:

Here's how the data flow would work from start to finish:

First, the raw data. This is just 2 days with 2 plants, 3 alarms and 3 turbines per plant:

So the next step is to count total number of alarms per plant for the given date range. Ultimately, we only care about the top 2 for each plant:

We now need to count the total number of alarms per turbine, to know which match up with our most common alarms:

Now we need only those which were part of the most common alarms (AC1, AC2):

And then again, the final data set which shows the most common alarms per plant, with a column for the turbine(s) which were most affected by those alarms:

I feel like this shouldn't be too difficult to do, it took about 2 minutes wo work up Access queries to show the data I need, but the tricky part is dealing with selecting any date range to begin with. In my example here it's just 2 days and I selected them both, but in reality we'll have year(s) worth of data and the user should use a slider to pick any date range they want, and then these same calculations need to happen.

Thank you so much if you can help!

thanks

Alex

[itsmebvk · Accepted solution]

@Anonymous

Here is what I am doing,

1)Create a measure called "Rank Alarm Code" using following code :

Rank Alarm Code = RANKX(ALLSELECTED(Sheet1[Alarm_Code]),CALCULATE(sum(Sheet1[Alarm_Count]),ALLSELECTED(Sheet1[Turbine])),, DESC) 

2)Create another measure called "Rank Turbines" using following code :

Rank Turbine = RANKX(ALLSELECTED(Sheet1[Turbine]),CALCULATE(SUM(Sheet1[Alarm_Count])))

3)Create one more measure called "Total Alarms" using following code:

Total Alarms = CALCULATE(SUM(Sheet1[Alarm_Count]),ALL(Sheet1[Turbine]))

4) Filter (visual/Report/Page) on "Rank alarm Code"<=2

5) Filter (visual/Report/Page) on "Rank Turbine"=1

6)Add "Total Alarms" to the report.

You should get output as shown in the screen shot.

I am also trying to explore PowerBI more, please correct me if i misunderstood your requirement. I was unable to upload PBX as i don't have permissions.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  For Plant 2, AC3 should also show up because of a tie - 32 alarms.

Hope this helps.

[Greg_Deckler]

Seems doable, but I am not typing in all of that data into Power BI Desktop to figure it out. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

I just got back to my desk and haven't followed that link yet (but surely will, thank you!), but assume you want the data - I should have thought of that. Attached is a spreadsheet with the original base data, as well as all of the data used in my prior images. Hopefully that helps, and if it does, thank you very much!

Alex

p.s. I couldn't figure how to upload an Excel file so I did read the above post on how to get the best possible answer, so I'll paste from excel below and hope for the best...

Raw Data
Plant	Date	Alarm_Code	Turbine	Alarm_count
Plant1	1/1/2018	AC1	T1	5
Plant1	1/1/2018	AC1	T2	1
Plant1	1/1/2018	AC1	T3	7
Plant1	1/1/2018	AC2	T1	1
Plant1	1/1/2018	AC2	T2	10
Plant1	1/1/2018	AC2	T3	3
Plant1	1/1/2018	AC3	T1	1
Plant1	1/1/2018	AC3	T2	10
Plant1	1/1/2018	AC3	T3	3
Plant1	1/2/2018	AC1	T1	21
Plant1	1/2/2018	AC1	T2	0
Plant1	1/2/2018	AC1	T3	12
Plant1	1/2/2018	AC2	T1	5
Plant1	1/2/2018	AC2	T2	9
Plant1	1/2/2018	AC2	T3	1
Plant1	1/2/2018	AC3	T1	1
Plant1	1/2/2018	AC3	T2	2
Plant1	1/2/2018	AC3	T3	5
Plant2	1/1/2018	AC1	T1	6
Plant2	1/1/2018	AC1	T2	15
Plant2	1/1/2018	AC1	T3	14
Plant2	1/1/2018	AC2	T1	6
Plant2	1/1/2018	AC2	T2	15
Plant2	1/1/2018	AC2	T3	2
Plant2	1/1/2018	AC3	T1	8
Plant2	1/1/2018	AC3	T2	6
Plant2	1/1/2018	AC3	T3	14
Plant2	1/2/2018	AC1	T1	20
Plant2	1/2/2018	AC1	T2	17
Plant2	1/2/2018	AC1	T3	8
Plant2	1/2/2018	AC2	T1	4
Plant2	1/2/2018	AC2	T2	20
Plant2	1/2/2018	AC2	T3	20
Plant2	1/2/2018	AC3	T1	5
Plant2	1/2/2018	AC3	T2	3
Plant2	1/2/2018	AC3	T3	18

Top 2 Alarms by plant
Plant	Alarm_Code	Alarm_count
Plant1	AC1	46
Plant1	AC2	29
Plant1	AC3	22
Plant2	AC1	80
Plant2	AC2	67
Plant2	AC3	54

All alarms by Turbine
Plant	Turbine	Alarm_Code	Alarm_Count
Plant1	T1	AC1	26
Plant1	T1	AC2	6
Plant1	T1	AC3	2
Plant1	T2	AC1	1
Plant1	T2	AC2	19
Plant1	T2	AC3	12
Plant1	T3	AC1	19
Plant1	T3	AC2	4
Plant1	T3	AC3	8
Plant2	T1	AC1	26
Plant2	T1	AC2	10
Plant2	T1	AC3	13
Plant2	T2	AC1	32
Plant2	T2	AC2	35
Plant2	T2	AC3	9
Plant2	T3	AC1	22
Plant2	T3	AC2	22
Plant2	T3	AC3	32

Join turbine data back to top alarms to find most impacted Turbines
Plant	Alarm_Code	Turbine	TotalAlarms	TurbineAlarms
Plant1	AC1	T1	46	26
Plant1	AC1	T3	46	19
Plant1	AC1	T2	46	1
Plant1	AC2	T2	29	19
Plant1	AC2	T1	29	6
Plant1	AC2	T3	29	4
Plant2	AC1	T2	80	32
Plant2	AC1	T1	80	26
Plant2	AC1	T3	80	22
Plant2	AC2	T2	67	35
Plant2	AC2	T3	67	22
Plant2	AC2	T1	67	10

Final dataset
Plant	Alarm_Code	TotalAlarms	Most impacted Turbine(s)
Plant1	AC1	46	T1
Plant1	AC2	29	T2
Plant2	AC1	80	T2
Plant2	AC2	67	T2

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  For Plant 2, AC3 should also show up because of a tie - 32 alarms.

Hope this helps.

[Anonymous]

@Ashish_Mathur I've started converting the work you did (thanks again, this is so helpful!) into a more realistic data set, changing column names to what I need them to be, etc. and I notice one issue and can't figure out how to fix it. I've uploaded a new copy of the .pbix here: https://drive.google.com/drive/folders/1al0BHPaSDibkgi5Axxgm_aIILKZLo0jE

My problem is that for plant2, the "test" filter, which is supposed to only show the top ranked turbines, returns a 0 for the top ranked turbines in this case. Alarm 4110 is correctly ranked #1, but none of the turbines which use that alarm are listed. Realistically, all 3 of them should have test = 1, as those are tied to the top ranked alarm. Any ideas you have are much appreciated, I can't quite figure it out. Thank you!

[Ashish_Mathur]

Hi,

That weblink reads "You need permission".

[Anonymous]

So I believe I've found the problem, but haven't yet found the solution. In "Top 2 Turbines 4.pbix" which can be found here: https://drive.google.com/drive/folders/1al0BHPaSDibkgi5Axxgm_aIILKZLo0jE

I noticed that one plant with many turbines works fine if I only select that specific alarm code, as shown below - it does correctly pick the only turbine which has that alarm twice:

And if I add another alarm which doesn't use any of the same turbines, such as 3661, it still works:

But if I choose an alarm which ONLY uses one of the turbines that's also part of 3634, then my "highest number of alarms at Alarm_code level" incorrectly goes to 3, which breaks the test filter.

So it seems to me that "highest number of alarms at Alarm_code level" needs some type of filter to only associate the alarms with the correct turbines, or vice versa. I'm still trying to understand precisely how these measures work, I may be able to figure this one out now that I see the problem, but if you have time to take a look it's much appreciated! This is a big project and I really need to get this done ASAP.

thanks again!

Alex

[Ashish_Mathur]

Hi,

See if this helps.  Download the PBI file from here.

[Anonymous]

Unfortunately that doesn't do it, now the "test" column is broken, and "highest number of alarms at alarm code level" is off in some cases. That value should be 2 for all of the 3634 rows, since turbine F09 has 2 of those alarms. If that column did have 2 in it, then our test which compares the highest column to number of alarms would only be true for F09, which is what we want. But now it's true for all of them. It's also now wrong for 3674.

I'll keep looking at it and see if I can make it work given these new changes. Thanks again for trying, and if you have any other ideas I certainly appreciate it!

[Ashish_Mathur]

Hi,

Does this work?

Highest number of alarms at Alrm_code level = MAXX(TOPN(1,ALL(top_alarms[turbine_code]),[Number of alarms]),[Number of alarms])

[Anonymous]

I will try it but I don't think it will work, as the problem seems to be with the Highest number of alarms at Alrm_code level measure, as described 2 posts back, where it increases to 3 when another turbine is introduced with the same alarm code, even though there are only 2 alarms on one turbine from 3634, not 3. It seems to me that the test measure will work correctly if I can get Highest number of alarms at Alrm_code level to work properly - it needs to only apply to a specific alarm code, and not increase when another alarm is introduced to the data set. I just can't figure out how to do that!

[Anonymous]

I can't figure out how to make permission public. Is there any chance you can email me at pozzyal@gmail dot com to request permission? Someone else above did that and it worked, and I thought they had it working, but now that I play with bigger data sets, that doesn't seem to be the case, so I'm still trying to figure it out.

Or is there a better way to get you the data file?

thanks!

Alex

[Anonymous · Accepted solution]

Wow! This is fantastic, thank you so much!

[Ashish_Mathur]

You are welcome.

[itsmebvk · Accepted solution]

@Anonymous

Here is what I am doing,

1)Create a measure called "Rank Alarm Code" using following code :

Rank Alarm Code = RANKX(ALLSELECTED(Sheet1[Alarm_Code]),CALCULATE(sum(Sheet1[Alarm_Count]),ALLSELECTED(Sheet1[Turbine])),, DESC) 

2)Create another measure called "Rank Turbines" using following code :

Rank Turbine = RANKX(ALLSELECTED(Sheet1[Turbine]),CALCULATE(SUM(Sheet1[Alarm_Count])))

3)Create one more measure called "Total Alarms" using following code:

Total Alarms = CALCULATE(SUM(Sheet1[Alarm_Count]),ALL(Sheet1[Turbine]))

4) Filter (visual/Report/Page) on "Rank alarm Code"<=2

5) Filter (visual/Report/Page) on "Rank Turbine"=1

6)Add "Total Alarms" to the report.

You should get output as shown in the screen shot.

I am also trying to explore PowerBI more, please correct me if i misunderstood your requirement. I was unable to upload PBX as i don't have permissions.