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