Home Forums Power Pivot Many-to-Many Relationship

This topic contains 6 replies, has 3 voices, and was last updated by  SergeiJ999 1 month, 1 week ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #10696

    rmelvin
    Participant
    • Started: 2
    • Replies: 6
    • Total: 8

    Hi

    Attached is a simple example of the data I have, and the output I am hoping to achieve.

    Each Invoice has one customer and multiple Units or products.

    Each unit/product is linked to a timesheet, where a single worker worked on a group of units over a certain timeframe.

    What I am looking to be able to do is summarize Timesheets that are linked to a particular unit grouping.

    In the example I have given, I filtered the data for timesheets that contain jewelry.  Essentially, the output would have one entry for every Jewelry unit sold, and group all the activity on that timesheet together in an output.  You’ll notice because timesheet T001 had two pieces of jewelry, sold to two different customers, that timesheet T001 is listed twice, under both customers.  The grand total would be calculated to include each unit only one time, for a net Revenue/Cost.

    I want a pivot table output that can quickly switch based on a unit grouping slicer, and instead of only including units that are jewelry, it includes all of the details of the related timesheet for each item.

    To put it as simply as possible: In this example, three pieces of Jewelry were sold. Show me the timesheets where those units were created, grouped by the customer who bought the unit (or the invoice the unit was from).

    If any clarification is needed at all, please let me know. perhaps this is a very basic many to many situation, but I am not sure.

    Thanks in advance.

    Attachments:
    You must be logged in to view attached files.
    #10699

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Check the desired results: Unit # U004 in the desired results shows blank under Cust, implying same as Cust A. U004 is part of Invoice I002 which is for Cust B.

    Also, desired results show Timesheet T002, which does not have any Jewelry items (only phones and cookware).

    #10702

    rmelvin
    Participant
    • Started: 2
    • Replies: 6
    • Total: 8

    Thanks for pointing out these things.

    T002 is a mistake.  It should be T001.  No matter how many things i checked, one mistake had to slip through.

     

    Think of the green output table as a pivot table output, and the blank rows under cust and timesheets are groupings the subsequent rows are grouped under.  The customer showing in the first column is the customer related to the jewelry unit in that timesheet. Cust A purchased two units of jewelry:  One made on timesheet T001 and the other on timesheet T003.  I want the ability to view that whole timesheet(s) grouped dynamically under the customer linked to the selected product grouping.

    The reason T001 is listed twice is because two pieces of jewelry were worked on, to two different customers.  So the entire timesheet is listed twice, under two seperate customers.  This is useful for us, as profitability is best viewed for us as margin made on a timesheet.  And being able to quickly see what customers are linked to positive margin timesheets, and what customers are linked to negative margin timesheets, is vital.

    So Cust A purchased two pieces of jewelry, and the overall margin of the timesheets related to those products is $____.

    Appreciate the request for clarification, to  help move this forward.

    #10708

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Here is the issue your current design faces: you cannot slice on Unit Group in the data table and then have all the rows from the data table linked to the parent timesheet table appear in the pivot (filter flow in the data model does not work like this).

    What you could do is create a bridge table linked to Timesheets that contains all actual pairs of unit group and timesheet ID. The measures for cost and revenue will need to implement m2m syntax as shown in the attached workbook. You can read about m2m syntax at this link (please be sure to read the notes below the post, especially those by Marco Russo).

    The pivot was arranged differently from the “desired results” so you could compare inputs and outputs.

    Attachments:
    You must be logged in to view attached files.
    #10718

    rmelvin
    Participant
    • Started: 2
    • Replies: 6
    • Total: 8

    This is very helpful.

    I figured M2M relationship and a bridge table would be necessary.  Just wasn’t sure where to start, and figured it was one step more complex.

    The output you created I think is very close.  But the one thing this doesn’t do is group by the customer/unit related to the unit grouping selected. Having it dynamically know what customer to group it under seems difficult in my head.

    When you pick jewelry, it should understand there were three jewelry items made:

    U002 to Cust A  timesheet T001

    U003 to Cust A  timesheet T003

    U004 to Cust B timesheet T001

    so the ideal output essentially has one row for each jewelry unit, and summarizes the matching time sheet activity underneath it. (the current output has one row for each timesheet, not each unit, meaning we can’t link it to a customer).

    This ideal output allows us to see what customers purchased jewelry, and how profitable the related time sheets were.  some customers are going to be linked to unprofitable timesheets frequently (with our realworld data).

    I understand that means timesheet T001 would be listed twice in the pivottable output, under two separate units/customers. But that is okay.  and I may possibly use the output you created if i want a version that contains no duplicate timesheets, which may be necessary and useful in some circumstances.

    Would this customer/unit grouping be possible with a different bridge table?

     

     

    #10721

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    A different bridge table would not make your grouping possible.

    I have seen some advanced sorting techniques in Power Pivot where pivot table sorting is set to a ranking column (and the values in the ranking column change based on slicer selection), and then the ranking column is hidden from view.

    You could also explore results displayed across multiple pivot tables, all controlled by a single slicer.

    You could also evaluate Power BI Desktop which has more cross-filtering options than Excel.

    #11262

    SergeiJ999
    Participant
    • Started: 0
    • Replies: 1
    • Total: 1

    I’d like some help on a similar topic and I hope this will be the right place to enter this question.

    I’ve built a data model – as shown in the attachment and I am attempting to do a powerpivot table that will show the number of medicare items processed for October, against the Number of morning and afternoon sessions that a doctor worked in the month of October.  The tables are shown in the attached picture.

    I have 2 main tables, which are:

    1. the medicare table where I can sum up the number of MBS Items (medicare claims) for the month by each provider; and
    2. the Sessions table which shows number of morning and afternoon sessions done by the doctor during the month.

    Unfortunately, the Sessions table uses a Nick Name for the doctor’s name, so “Provider Name” (in the medicare table) is equivalent to “Medicare name” in the Employees Table.  And the name of the doctor differs slightly between Employee Name and Medicare Name.

    So I built the SessionsName table which connects the Medicare Table to the Sessions Table.  The following is the pivot table and I have tried a number of DAX formulas to connect the 2 sets of data together.

    the measure “No of MBS Items” = =COUNTROWS(Medicare)

    the measure “No of Sessions” =sum(Sessions[No. Sessions])

    the measure “October Sessions” =CALCULATE([No. of Sessions], ‘Calendar'[MonthName] = “October”)

    As you can see from the pivot below, the last 2 measures are matched against the “Blank” – which would be other types of professionals who have processed claims in the month of October.  What am I doing wrong?  Would greatly appreciate  understanding this better.
    <table width=”840″>
    <tbody>
    <tr>
    <td width=”228″></td>
    <td width=”204″>MonthNo</td>
    <td width=”204″>MonthName</td>
    <td width=”204″>Values</td>
    </tr>
    <tr>
    <td></td>
    <td>10</td>
    <td>10</td>
    <td>10</td>
    </tr>
    <tr>
    <td></td>
    <td>October</td>
    <td>October</td>
    <td>October</td>
    </tr>
    <tr>
    <td>Employee Name</td>
    <td>No MBS Items</td>
    <td>No. of Sessions</td>
    <td>October Sessions</td>
    </tr>
    <tr>
    <td>ALISON CHEW</td>
    <td>284</td>
    <td></td>
    <td></td>
    </tr>
    <tr>
    <td>ANDREW ROWAN</td>
    <td>590</td>
    <td></td>
    <td></td>
    </tr>
    <tr>
    <td>ARAUZ CLAUDIA</td>
    <td>606</td>
    <td></td>
    <td></td>
    </tr>
    <tr>
    <td>DEBALINA GON CHAUDHURY</td>
    <td>496</td>
    <td></td>
    <td></td>
    </tr>
    <tr>
    <td>DEEPA PUSHPANGADAN</td>
    <td>587</td>
    <td></td>
    <td></td>
    </tr>
    <tr>
    <td>DHARMARETNAM SESHAKUMARAN</td>
    <td>550</td>
    <td></td>
    <td></td>
    </tr>
    </tbody>
    </table>
    <table width=”840″>
    <tbody>
    <tr>
    <td width=”228″>YU AYE</td>
    <td width=”204″>454</td>
    <td width=”204″></td>
    <td width=”204″></td>
    </tr>
    <tr>
    <td>ZAREEN BAQAR</td>
    <td>218</td>
    <td></td>
    <td></td>
    </tr>
    <tr>
    <td>ZHANG WEI</td>
    <td>389</td>
    <td></td>
    <td></td>
    </tr>
    <tr>
    <td>(blank)</td>
    <td>1794</td>
    <td>802</td>
    <td>802</td>
    </tr>
    </tbody>
    </table>
     

    Attachments:
    You must be logged in to view attached files.
Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.