Get the highest average

[Syndicate_Admin]

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:

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!!

[v-henryk-mstf]

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

[TheoC]

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]

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