Using data from two tables with different granularity
Results 1 to 5 of 5

Thread: Using data from two tables with different granularity

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using data from two tables with different granularity

    Hi,
    I have two data tables. One has actual sales by Customer and Product code (Sales_By_Customer). The second (Sales Forecast), contains a total sales forecast by Customer only. I'm trying to calculate and chart the variance to target for each customer. I've created this measure:


    Target v actual £s = sum(Sales_By_Customer[Total Sales Value £s])-sum('Sales Forecast'[Sales Forecast])


    This works when I use a Card visualisation to display the total variance, but when I try and create a bar chart to show variance by individual customer the chart shows complete rubbish!.


    I think the issue is probably caused by using data at different levels of granularity.


    Does anybody have any ideas/pointers that may help me solve this issue please?


    Thanks


    Pete

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Using data from two tables with different granularity

    Hi Charlklands

    I cannot be of help without seeing your data. If you post some sample data then I can take look otherwise you could take a look at: https://www.daxpatterns.com/handling...granularities/
    Excel and BI blog: http://xcelanz.com/

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using data from two tables with different granularity

    Hi,
    Example of the data in each table:

    Table 1
    Sales Forecast
    Customer Month Sales Forecast
    Customer X 01/11/2018 £728,945
    Customer Y 01/11/2018 £271,203
    Customer Z 01/11/2018 £165,207
    Customer AA 01/11/2018 £96,732
    Customer AB 01/11/2018 £41,923

    Table 2
    Sales_By_Customer
    Invoice_Date Invoice Number Product code Description Customer Name Total Sales Value £s
    01/11/2018 43294 7227 Product A Customer X £20,000
    01/11/2018 42785 7228 Product B Customer Y £15,000
    01/11/2018 46064 71120 Product C Customer Z £13,000
    01/11/2018 43578 7771 Product D Customer AA £25,000
    02/11/2018 40749 7227 Product A Customer X £11,000
    02/11/2018 41971 7771 Product D Customer Y £45,000
    02/11/2018 44833 7286 Product E Customer Z £20,000
    02/11/2018 43701 7286 Product E Customer X £15,000


    Hope this helps!

    Pete

  4. #4
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Using data from two tables with different granularity

    You need to create a Customer dimension which filters both tables. Then you can slice easily by Customer.

    In the link I pasted there're also techniques which explain how not to display the forecast if you're slicing data by something else which is not a customer, like product in your example.

    Excel and BI blog: http://xcelanz.com/

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using data from two tables with different granularity

    Quote Originally Posted by VBA Geek View Post
    You need to create a Customer dimension which filters both tables. Then you can slice easily by Customer.

    In the link I pasted there're also techniques which explain how not to display the forecast if you're slicing data by something else which is not a customer, like product in your example.

    Thanks for your help, it all works now.

    Thanks again!

    Pete

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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