+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    05-27-2020
    Location
    Guangzhou-China
    MS-Off Ver
    Office 2016
    Posts
    22

    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
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,502

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    05-27-2020
    Location
    Guangzhou-China
    MS-Off Ver
    Office 2016
    Posts
    22

    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. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,502

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Power pivot, summarising a calculated field (Volume, rate, mix)
    By ecudc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-26-2019, 12:15 PM
  2. Add Calculated Field from Power Pivot
    By kyingh in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-11-2019, 01:52 AM
  3. [SOLVED] Power pivot or pivot table for connecting data and creating calculated fields
    By jaryszek in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 02-06-2019, 08:31 AM
  4. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  5. Error in power pivot calculated field
    By stephme55 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-27-2016, 01:42 PM
  6. adding a calculated field to power pivot data model
    By stephme55 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-26-2016, 07:13 PM
  7. Pivot Table FRom Power Pivot Returns Sum Of Entire Field
    By goss in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-26-2013, 07:34 PM

Bookmarks

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