none
How to group data from multiple tables

    Question

  • I have the following structure in Powerpivot.

    EmployeeTable contains employee data. LeaveAvailableTable contains number of leaves that an employee can take. LeaveTable contains leave that employee has taken. Configuration contains leave title and code for e.g. 01, Annual; 02, Casual

    I want to display a report like this:

    EmployeeCode, EmployeeName, LeavesAvailable, LeavesTaken, RemainingBalance

    I am able to do a report where I can display everything except RemainingBalance. I have even done remaining balance part but not by adding a column in PowerPivot and instead just using Excel standard formula subtracting LeavesAvailable with LeavesTaken to come up with ReminingBalance for e.g. C1-D1 but this is not neat.

    I want to add a calculated column/field in PowerPivot table but don't know how to do that when there are so many joins.

    Friday, November 10, 2017 5:47 AM

All replies

  • Hi Frank Martin Consultant,

    Thanks for your question.

    In this scenario,you can delete all the relationships for table Configuration. Then you can use DAX LOOKUPVALUE function to get LeaveTypeValue for LeaveTable, and get LeaveTypeTitle for LeaveAvailableTable.
    In table LeaveTable, create a calculated column called LeaveTypeValue as below:

    LeaveTypeValue:= LOOKUPVALUE('Configuration'[LeaveTypeValue],
     'Configuration'[LeaveTypeTitle],
    'LeaveTable'[LeaveTypeTitle])


    In table LeaveAvailableTable, create a calculated column called LeaveTypeTitle as below:

    LeaveTypeTitle:= LOOKUPVALUE('Configuration'[LeaveTypeTitle], 
    'Configuration'[LeaveTypeValue],
    'LeaveAvailableTable'[LeaveTypeValue])

    Then you can refer to below thread to create related calculted columns and measures.
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/319df507-cebe-49ae-909f-1e0dc99bdc3c/how-to-join-multiple-sharepoint-lists-in-power-pivot?forum=sqlkjpowerpivotforexcel#dfa85b87-996f-4975-ab97-ead4a6f333f4

    For RemainingBalance measure, see below DAX formula:

    Remainbalance:=[SumofTotalLeavesAvailable]-[SumofTotalLeavesTaken]

    In the end, you can just drag the related fields to the Power Pivot report, see below image:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Friday, November 10, 2017 11:51 AM
    Friday, November 10, 2017 8:36 AM
  • When I used your solution to used LOOKUP instead of join then I was getting wrong values. So what I did is kept joins and dropped columns on report in certain order and now report is perfect EXCEPT RemainingBalance part.

    Following is how my report looks like now.

    To show RemainingBalance I added a new column LeavesAvailable' in 'LeaveTable' which is actually coming from 'LeaveAvailableTable' and it is also displaying fine as below.

    But as you can see once 'RemainingBalance' column is showing in correct value. It should show value like I mentioned in red color.


    Friday, November 10, 2017 10:57 AM
  • Hi,

    You should follow willson's solution, doing all the calculation in table LeavesAvailable. Adding calculated column TotalLeavesTaken and LeaveTypeTitle to table LeavesAvailable,create all the measures in table LeavesAvailable.

    Friday, November 10, 2017 11:51 AM
  • Hi,

    You should follow willson's solution, doing all the calculation in table LeavesAvailable. Adding calculated column TotalLeavesTaken and LeaveTypeTitle to table LeavesAvailable,create all the measures in table LeavesAvailable.

    When I use Willson's solution than I get wrong figures for NoOfLeavesTaken as shown below.

    Saturday, November 11, 2017 6:30 AM
  • Hi Frank Martin Consultant,

    Thanks for your response.

    According to the relationship waring in the image provided by you, It seems like you did not follow all my steps, especially the step to add a calculated column called [TotalLeavesTaken] in table LeavesAvailable as below:

    sumx(filter('Leaves', 'Leaves'[EmployeeID] = LeavesAvailable[EmployeeID] && 
    'Leaves'[LeaveType] =LeavesAvailable[LeaveType]),
    'Leaves'[NoOfLeavesTaken])

    If all the facts(Total_AvailableLeaves,Total_NoOfLeavesTaken) are stored in table LeavesAvailable , then you will not encounter this relationship waring, and the RemainingBalance measure will be calculated correctly.

    Please first create a calculated column called called [TotalLeavesTaken], then you can create a measure called SumofTotalLeavesTaken in table LeavesAvailable as below:

    SumofTotalLeavesTaken:=SUM([TotalLeavesTaken])


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, November 13, 2017 7:13 AM