Power Pivot : DAX formula Max of a Sum calculated field of power pivot

Hello everyone,

I am searching on internet since one day and i cannot find a solution to fix my problem,I have a power Query table as source and a power pivot to sum up the information, the power pivot sum up the working time of the employee in accordance with the filter timeline slicer, the employee are split by group, that mean each group will contain few employee. I am looking to show up in another column the max working time of each group in accordance with the timeline filter but i can t achieve this result.

For the moment I just get the max worktime of each group but for one day only, that mean if I choose one month on the timeline filter it will still show me the max time of one day of the group.

This is my DAX formula (I precise that I use excel to build DAX formula and not power BI Desktop :
=CALCULATE(max(SalaryDataBase[Work time : Normal 正常工作时间 (H)]),ALLEXCEPT(SalaryDataBase,SalaryDataBase[Production line split for pivot table],SalaryDataBase[Date 日期]))

I need this information to sum up the equivalent workers qty in accordance with time filter by using a simple formula : Normal working time of each employee of a group / Max working time of the group but I need to determine the Max working time of the group first.

I hope it s enough clear, I include the file attached for a better understanding (I reduced the data because the file was too heavy): "SALARY ANALYSE" contain the pivot table and "Salary Pivot & Query data base" contain the query table source.

Let me know if need more informations,

Geoffrey

Hello JeteMc,

Thank you for your feedback, I finally found out a way that work for me, I used from Data model of Pivot table to access to the formula Distinct count.

Geoffrey