0

I have loaded 2 tables into Power Pivot which are related by a key. One table contains sales for a product and the other contains it's cost. What I am trying to do is multiply the total sales by the cost of that item. How do I write a calculated measure in a pivot table that would calculate this?

= SUM(ProductSales[Sales])*RELATED(MarketValue[Value])

The error I am getting is that This formula is invalid or incomplete: 'The column 'MarketValue[Value]' either doesn't exist or doesn't have a relationship to any table available in the current context.'

I understand that this is wrong but i'm not sure how to modify it to suit my needs. I tried using SUM in front of the related function but it wouldn't accept the related function.

Thanks in advance.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
Sean D
  • 71
  • 1
  • 2
  • 5

1 Answers1

1

If there is no relationship between the tables, you should have a common field at least, i.e. ProductSales[ProductID] and MarketValue[ProductID]. Using the common field you can join both tables using FILTER function.

=SUMX (
    ProductSales,
    ProductSales[Sales]
        * CALCULATE (
            VALUES ( MarketValue[Value] ),
            FILTER ( MarketValue, MarketValue[ProductID] = ProductSales[ProductID] )
        )
)

UPDATE: If you managed to create the relationship in your model, you have to create three measures:

  1. Cost measure in the MarketValue.

    = SUM(MarketValue[Value])

  2. Sales measure in the ProductSales

    =SUM(ProductSales[Sales])

  3. Then just use the two previous measures.

    = [Sales]*[Cost]

Let me know if this helps.

Community
  • 1
  • 1
alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Hi Alejandro, this is great, thanks. Say if there was a relationship, what would the formula be? – Sean D Sep 12 '16 at 15:42
  • @SeanD, I've updated my answer based on your comment. – alejandro zuleta Sep 12 '16 at 16:14
  • Alejandro, it works great thank you for taking the time to get back to me... pretty easy when you know how! But, I am having one more problem where my grand total isn't adding up properly. I'll give you an example, I have sales for each year 2014 through to 2018 and each year the price of the item changes. The grand total is doing: (the sum of all sales throughout the years) * (the sum of all the values throughout years) Where ideally, it would be adding (2014 sales * 2014 value) + (2015 sales * 2015 value) and so on. Is there a way to achieve this? Many thanks. – Sean D Sep 12 '16 at 18:04
  • @SeanD, Do you have a Date table market? If so it should work. It seems you have a date column for `ProductSales` and another date column for `MarketValue`. You can build a date table and which you have to relate to `ProductSales[Date]` and `MarketValues[Date]` columns. For further help add a image of your model and explain the visualization you are after. – alejandro zuleta Sep 12 '16 at 18:46
  • Thanks, that would be great. I downloaded a date table leaving only the years, datekey and full date, I then converted my "years" column to date and created the relationship between my two tables and the date table. Please see the images of my data model and the relationships created in the Imgur album http://imgur.com/a/Wifpz In my pivot table, I am summarising by year from the date table, but still my grand totals are out, if you could could point me in the right direction as to why, I would be massively grateful - and thanks in advance for all your time already. – Sean D Sep 13 '16 at 10:23
  • Also, I found this link on StackOverflow, http://stackoverflow.com/questions/37935238/how-to-refer-to-a-single-value-in-a-calculated-column-in-a-measure-dax Should I be using a similar approach as to the one described in the answers? – Sean D Sep 13 '16 at 11:06
  • @SeanD, I see your issue is related to the relationship in your model. You should have a Date table with a date column containing unique dates. Your tables should have a date column related to Date table through date column. Then you can add to your rows the year column in your date table and the measures I created in my answer. – alejandro zuleta Sep 14 '16 at 02:21