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,

 

That weblink reads "You need permission".

Anonymous's avatar
Anonymous
Not applicable
7 years ago

So I believe I've found the problem, but haven't yet found the solution. In "Top 2 Turbines 4.pbix" which can be found here: https://drive.google.com/drive/folders/1al0BHPaSDibkgi5Axxgm_aIILKZLo0jE

 

I noticed that one plant with many turbines works fine if I only select that specific alarm code, as shown below - it does correctly pick the only turbine which has that alarm twice:

 

 

And if I add another alarm which doesn't use any of the same turbines, such as 3661, it still works:

 

 

But if I choose an alarm which ONLY uses one of the turbines that's also part of 3634, then my "highest number of alarms at Alarm_code level" incorrectly goes to 3, which breaks the test filter.

 

 

So it seems to me that "highest number of alarms at Alarm_code level" needs some type of filter to only associate the alarms with the correct turbines, or vice versa. I'm still trying to understand precisely how these measures work, I may be able to figure this one out now that I see the problem, but if you have time to take a look it's much appreciated! This is a big project and I really need to get this done ASAP.

 

thanks again!

Alex

  • 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])