Can't seem to get this working: calculations per day on metrics from multiple datasheets

[Anonymous]

First of all, sorry for the long story.

Let me try to outline what I have, what I want and what I did so far.

What I have

I have 4 datasets/tables loaded into PowerBI:

1. A table in which day-to-day data is displayed based on several metrics, the most important one being spend (cost).

2. A table in which day-to-day data is displayed based on the number of conversions that have been generated.

3. A table containing meta-data from our campaigns like start date, end date, budget etc.

4. A table containing meta-data which we enter ourselves, the most important one being KPI goal.

What I want

What I want is to create a dashboard with which we can manage our campaigns more efficiently. Because we report on several metrics to our clients it's important for us to monitor these metrics on a daily basis. One of these metrics is the Cost per conversion. So basically I want to do a few things:

a. Show what the value of these metrics (like cost per conversion) is over the entire lifetime of the campaign.

b. Show how the value of these metrics have fluctuated over the course of time (e.g. using a line and clustered column chart).

What I did so far

I have the following relations in place:

Table 1 links to table 3.*

Table 2 links to table 3.*

Table 3 links to table 4.

*I can't link tables 1 and 2 directly because none of the datasets have unique values.

In dataset 2, there's a column showing which conversion pixel was loaded in order to generate a conversion. In the end we will want to be able to filter on the pixels. So the first problem is that there are days that no conversions occur. Therefore, I can't simply use a function in which I divide the total spend by the total amount of conversions. Because when I would filter on a certain conversion pixel I would ignore some days of dataset 1 (since the campaigns would be filtered out).

So as a calculation for this cost per conversion, I decided to firstly create new measures for:

- Spend = CALCULATE(sum(Spend[Media Cost(D)]),all('Conversions'))

Ignoring all filters from the [Conversions] table.

So the above works based on the entire lifetime of the campaign. If I then want to show how the cost per conversion fluctuates over time, I fail. I suspect this is because there is no 'date correlation' between the two tables. Therefore, when I enter a line + column chart it only shows the total amount of conversions as a steady line. I tried to add a new table (date table) to link dates to this but PowerBI tells me I have a circular reference (obviously).

Who can help me?

P.S. The reason I explained everything instead of just what isn't working is because I'm having doubts about the way I set the relationships and the way I'm calculating the cost per conversion at the moment. So if there are easier/better ways to do this, please let me know.

[v-sihou-msft · Accepted solution]

@Anonymous

In this scenario, we can create a calendar table to make an indirect relationship between Table 1 and Table 2 (I noticed that you have already tried to do it but have a circular reference problem).

I have created some sample tables as below according to your description. Please try to follow the steps I listed and feel free to ask if you still have any question.

1. Create a calendar table and create relationships between Table1/Table2 and Calendar table.
   I only create two relationships here, you may need to create other relationships when you need to do some calculations related to budget and KPI goal.
   

   CalendarTable = 
   CALENDAR ( MIN ( Table3[Start date] ), MAX ( Table3[End date] ) )
   

   
2. Create one measure for cost per conversion over the entire lifetime of the campaign, and another measure for cost per conversion fluctuates over time (day-to-day).
   

   Cost_Per_Conversion_Entire_Lifetime = 
   CALCULATE (
       DIVIDE ( SUM ( Table1[Spend] ), SUM ( Table2[Number of conversions] ) ),
       ALL ( CalendarTable[Date] )
   )
   

   Cost_Per_Conversion = 
   DIVIDE ( SUM ( Table1[Spend] ), SUM ( Table2[Number of conversions] ) )
   

3. Drag a slicer and a line and clustered column chart into your canvas. And you can see the cost per conversion as below.
   

Regards,

[Anonymous]

Thanks Simon, got it working now.