Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

Get the highest average

Hello, good day.

I have a database, where I have the names of the order (OP), the department that belongs (resp x dep2), the days it carries in that order (EEC Days), and the tons.

I made a calculated column to calculate the average of the EEC Days, by order code and responsible department.

The formula is the sig:

Promed 2&3 = CALCULATE(
AVERAGE('DATA Base FAT2 & FAT3'[Dias CEE]),
ALLEXCEPT('DATA Base FAT2 & FAT3','DATA Base FAT2 & FAT3'[OP],'DATA Base FAT2 & FAT3'[resp x dep 2], 'DATA Base FAT2 & FAT3'[Reduccion Stock],'DATA Base FAT2 & FAT3'[MostrarDia]))
The formula works correctly.
The detail is that, when I put the column to the table, some orders may have two or more responsible and it is displayed like this:
BereniceMtz10_0-1632279298405.png

What I want is that it only shows the average in order to the department that has the highest tonnage, in the case of the photo, of the first order: 366774 would be that only show in Prom dias CEE: 2 for the Premium department. I already tried some things and nothing, hopefully they can help me.

Thank you!!

3 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    Hi Syndicate_Admin , if it's in Power BI, you should be able to simply go to the Filter pane, select the specific filter (i.e. Prom Dias CEE: 2) and click on Filter Type = Top N.  From there, select "Top" = 1 and filter by the respective value using Max of Prom Dias CEE: 2, then click "Apply Filter".

     

    If the above isn't what you were wanting, please upload a copy of the PBIX.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      Thanks for the comment but it did not work for me, since I already have a Top N filter from another section, and Power mentions me I can not have two. I will see if I can upload a copy of the power, it is very heavy

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi Syndicate_Admin ,

     

    The pbix file you provided is not displayed for the time being, whether it can be updated. I will solve it for you as soon as possible.


    Looking forward to your reply.


    Best Regards,
    Henry