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

1. ## 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.

Geoffrey

2. ## Re: Power Pivot : DAX formula Max of a Sum calculated field of power pivot

Perhaps this will be of some help.
The DAX formula is: Max Work Time:=max(SalaryDataBase[Work time : Normal 正常工作时间 (H)])
Note that while EEE worked 14.5 hrs. on 5/3, Yan Peng worked 31 hrs. on 5/4. Both values show up in the pivot table while the subtotal for production line E displays 31.
I feel that is the result that you are looking for, however if not then I would help us understand if you could give us some manually calculated results with which to base the outcomes of our proposed formulas.
Let us know if you have any questions.

3. ## Re: Power Pivot : DAX formula Max of a Sum calculated field of power pivot

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

4. ## Re: Power Pivot : DAX formula Max of a Sum calculated field of power pivot

I am glad that you found a solution. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1