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!