Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multiple group by/ranking issue

I have a faily simple data issue, which seems like it could be complicated in Power BI, but I'm a relative newbie so I'm hoping someone can point me in the right direction.   Here's the overview: I...
Need Help
  • itsmebvk's avatar
    itsmebvk
    7 years ago


    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    7 years ago

    Wow! This is fantastic, thank you so much!

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

 

Does this work?

 

Highest number of alarms at Alrm_code level = MAXX(TOPN(1,ALL(top_alarms[turbine_code]),[Number of alarms]),[Number of alarms])
Anonymous's avatar
Anonymous
Not applicable
7 years ago

I will try it but I don't think it will work, as the problem seems to be with the Highest number of alarms at Alrm_code level measure, as described 2 posts back, where it increases to 3 when another turbine is introduced with the same alarm code, even though there are only 2 alarms on one turbine from 3634, not 3. It seems to me that the test measure will work correctly if I can get Highest number of alarms at Alrm_code level to work properly - it needs to only apply to a specific alarm code, and not increase when another alarm is introduced to the data set. I just can't figure out how to do that!