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
Super User
7 years ago

Hi,

 

See if this helps.  Download the PBI file from here.

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Unfortunately that doesn't do it, now the "test" column is broken, and "highest number of alarms at alarm code level" is off in some cases. That value should be 2 for all of the 3634 rows, since turbine F09 has 2 of those alarms. If that column did have 2 in it, then our test which compares the highest column to number of alarms would only be true for F09, which is what we want. But now it's true for all of them. It's also now wrong for 3674.

 

I'll keep looking at it and see if I can make it work given these new changes. Thanks again for trying, and if you have any other ideas I certainly appreciate it!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super 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!