locked
Incorrect value shown despite proper relationship of linked tables RRS feed

  • Question

  • Hi, I have two tables as in the example file posted below:

    https://onedrive.live.com/redir?resid=744550B858A22804!609&authkey=!ABamlz36vHbuvgU&ithint=file%2cxlsx

    The first table has the number of sales per country and week. There can be several sales per country per week.

    The second table has a target amount for the total sales per country per week.

    Now I want to display a pivot table that shows me the sum of the sales per country per week and compares it to the Target.

    I setup a new column in both tables called Countryweek to have a unique identifier of the targets.

    However, just linking the two tables with the "Countryweek" column does not give me the proper amount in the pivot table.

    As you can see in the example file, I want to show the total sales filtered by week and one row per country, which is no problem. However, it does not show the correct Target number relating to that country and week.

    I have tried creating a new table via PowerQuery that shows me the Total Sales grouped by Countryweek, but it still does not work. How can I make this work?

    Thanks a lot and Happy New Year!

    Wednesday, December 31, 2014 4:32 PM

Answers

  • You have several options.

    The easiest is just to use the [week] and [country] fields from your target table rather than from the sales table.

    Context from relationships only flows in one direction in Tabular models. In this case since target is your lookup table, the context from target flows to sales. If you want to use the fields from sales instead, you can use these measures:

    SumSales:=
    SUM( sales[sales] )
    
    SumTarget:=
    CALCULATE(
        SUM( target[target] )
        , sales
    )

    In this case, you are forcing the relationship to flow in reverse (essentially) using sales as an argument to your CALCULATE() function. This only allows SUM() to be evaluated where there exists a row in context in the sales table.

    Your third alternative would be to create a third table, that being a dimension housing week and country, with both sales and target looking up to this third table, and using the fields from that table as the filters on the pivot.

    • Marked as answer by Mustikah Thursday, January 1, 2015 11:02 PM
    Wednesday, December 31, 2014 8:17 PM