none
City wise billing

    Question

  • Hi,

    In this workbook, I am trying to compute the city wise billing (sheet4) but cannot determine the relationship to be established.

    Please help.

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Friday, December 27, 2013 1:58 AM

Answers

All replies

  • Hi Ashish,

    May you explain what would you want to achieve with that data model? What is the meaning of the session_data-table compared to the Billing_data?

    Best regards,

    Julian

    Friday, December 27, 2013 1:35 PM
  • Hi,

    Thank you for replying.  The Session data tab contains training data which has 4 columns - Date of training, Clients, Location and participant name.  The Billing data tab contains Client wise, date of invoice, data of training (same as the one of the session data tab) and Amount billed.

    Now I want to know the Location wise revenue.  I create a Power Pivot in sheet4 but the result is incorrect because I could establish the correct Table relationships.

    I hope I have clarified the question.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Friday, December 27, 2013 2:49 PM
  • Okay, I understand. The Session_Data is your lowest level of data and multiple sessions are billed in combined invoices.

    Since your Location field is on the session-level how should the Amount Billed be "distributed" across sessions?

    Regards,

    Julian

    Friday, December 27, 2013 10:00 PM
  • Hi,

    I do not know what you mean by "multiple sessions are billed in combined invoices".  There is always one invoice per session.

    I simply want to determine the Location wise billing.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, December 28, 2013 12:05 AM
  • Maybe you can explain the relationship using an example?

    Fo example these session-data:

    12.03.2013 00:00:00 ABC New Delhi
    12.03.2013 00:00:00 ABC New Delhi

    ...how are they related to the billing_data? No billing_data exist for custmer ABC on that day; the "closest" match seems to be this billing-record:

    ABC 13.03.2013 00:00:00 12.03.2013 00:00:00 100

    That is why I assumed that multiple sessions are billing in combination.

    Regards,

    Julian

    Sunday, December 29, 2013 12:23 PM
  • Hi,

    The key between the two tables is Date of session.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, December 30, 2013 6:41 AM
  • Hi Ashish,

    Based on my understanding of the model, we need to evaluate the Revenue measure within the context of the ‘Date of session’ and the ‘Client’ columns. We are currently evaluating this measure within the context of the ‘Date of session’ column only because this is the only relationship currently defined.

    Client
    ABC
    FGB
    GHT

    A potential solution is to introduce a 'Client' look-up table (as shown directly above) containing the distinct list of Clients. We can then force the Revenue measure to be evaluated within the context of the Client[Client], in addition to the ‘Date of session’, using a DAX formula.

    The following shows this approach applied to the workbook you provided.  

    Below is the added measure where we have wrapped the existing Revenue measure in some DAX that forces it to be evaluated within the context of the client:

    RevenueM2M:=CALCULATE(
      [Revenue],
      SUMMARIZE(
        Session_data,
        Client[Client]  
      )
    )


    The result:

    I have previously posted in a thread with a similar approach where I have explained the behavior in more depth: Assistance required with a multiple vendor/single customer data model schema. There is also a popular whitepaper called 'The Many-to-Many Revolution' that goes into more depth with complex modelling scenarios such as this and it can be found here: http://www.sqlbi.com/articles/many2many.

    Hope this helps.

    Michael



    Monday, December 30, 2013 11:12 AM
    Answerer
  • Thank you.  I will try and post back problems, if any.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Friday, January 03, 2014 12:56 PM
  • Hi,

    I think I have solved this problem using another method.  You may download the workbook from the link shown in the last paragraph of my blog post at the following link - http://www.ashishmathur.com/create-a-pivot-table-from-multiple-individual-ranges-without-using-ancillary-columns/


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Sunday, January 05, 2014 2:24 AM
  • Hi Ashish,

    I'm glad that your were able to come up with an alternative solution/approach to your problem. Can you kindly outline it in this thread, even if it's just a copy and paste from your blog, so that it can be marked as the answer.

    In addition to this, if you found my above post helpful then you can vote it as being helpful.

    Thanks

    Michael

    Sunday, January 05, 2014 10:04 AM
    Answerer