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!
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.
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!
Hi,
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