Forum Discussion
Multiple group by/ranking issue
AnonymousHere 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"<=25) 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.
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.
Wow! This is fantastic, thank you so much!
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!
Super UserHi,
That weblink reads "You need permission".
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
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
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!