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!
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
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 |
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!
You are welcome.
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".