Measure: SUM columns from two different tables and divde

[umahm1]

Hi guys, 

I need your help at creating a measure. 

Two tables: Revenue and WorkingHours - they are NOT related. They have one connecting key which is the countryID. 

Above picture shows the workinghours table. 

The measure is very simple and looks like this: 

Measure X =CALCULATE(SUMX ( 'Revenue'; [DRevenue] / [Cost] )    / SUMX(WorkingHours; [Hours]))

It works fine, but the worked hours table contains hours for different countries, which leads to the Measure X calculating things wrongly because the denominator (SUMX(WorkingHours; [Hours])) is summing all country values. 

Sample data:

 
RevenueWorkingHours

[mobriladigo]

---

@umahm1 wrote:

Hi guys, 

I need your help at creating a measure. 

Two tables: Revenue and WorkingHours - they are NOT related. They have one connecting key which is the countryID. 

Above picture shows the workinghours table. 

The measure is very simple and looks like this: 

Measure X =CALCULATE(SUMX ( 'Revenue'; [DRevenue] / [Cost] )    / SUMX(WorkingHours; [Hours]))

It works fine, but the worked hours table contains hours for different countries, Audacity Find My iPhone Origin which leads to the Measure X calculating things wrongly because the denominator (SUMX(WorkingHours; [Hours])) is summing all country values. 

Sample data:

 
RevenueWorkingHours

---

As best practice it regards have nation measurement and connection these tables to that and after that your measure will work fine. You can make ths nation measurement from these table utilizing DAX or PowerQuery. Fill me in as to whether you require additionally help.

[umahm1]

@mobriladigo

Please help doing this using DAX

[parry2k]

@umahm1 you are saying country id is in both the tables and you want to create measure whcih calculates for each country, correct?

As best practice it is good to have country dimension and link these tables to that and then your measure will work fine.

You can create ths country dimension from these table using DAX or PowerQuery. Let me know if you need further help.

[umahm1]

Hi @parry2k.

The two tables mentioned are both facts. I do have a country dimension, but it won’t necessarily be used in the reports.
The measure still needs to only divide by the particular country and not all countries.

I forgot to mention that the WorkingHours table contains rows for each work day in 10 years period. So UK can have many rows with the same hour value.

[parry2k]

@umahm1 since you already have dimension table and you understand star schema, it will be very easy if you bring Country table in your model and wok thru this.

Let's do simple measures and go from there, assuming you already have [Revenue], [Cost], [Hours] measures calculated.

Revenue / Cost = Divide( [Revenue], [Cost], 0 )

Now drop country from dimension country table and [Revenue/Cost] and [hours] measure, you will notice each measure will be calculated for that specific country.

Add Another measure

Measure X = Divide( [Revenue / Cost], [Hours], 0 )

And once you drop this measure in the table visual mentioned above, you will find your calculation are all correct.

Having said that, bringing country dimentsion and linking with "Revenue" and "Working Hours" table will simply the things and is best practice to achive what you are looking for.

Hope it is helpful.

[umahm1]

Hi again @parry2k.

What if I don’t drop country into a visualisation? Would it still calculate the correct value for the measure ? I unfortunately don’t think it will.

[parry2k]

@umahm1 it will do it for all the countries. 

[umahm1]

Hmm. I don’t want that.

The report users don’t even want the country in the visualisation. The WorkingHours table is a fact/helper table. So I only want, for for example UK, the measure only sums the hours for UK.

Both tables are linked to a date dimension so I can aggregate on week, month and year etc.

[parry2k]

@umahm1 if you want only for UK, just filter on UK and it will work. Not fully sure what your exact requirements are.

[umahm1]

Ok. Let me explain.

The tables are not related. They have one column that is in both tables, the country key.

The WorkingHours table has many rows per country for each day.
I want the measure to first join the tables together on country key and then the sum of the dividend should be calculated for the rows where the country id is equal in both tables.

Maybe the earlier function can be used.

[parry2k]

@umahm1 the solution which I described earlier will work. Thanks

[umahm1]

You solution works fine when the country dimension is dropped in, but without it the calculation is wrong.
It seems like it does this: revenue/cost/hours = 10000/1000/(uk hours + us hours + fr hours). Probably because it can’t find a link between the tables as they are not related.

[parry2k]

@umahm1 you have to share the sample dataset.

[umahm1]

@parry2k

Just updated the OP

[umahm1]

I tried the solution and it works when dropping in the country dimension, but without it it does not calculate correctly.
It seems like it does this: Revenue(10000)/Cost(1000)/(uk hours+Us hours+fr hours) it’s probably because The tables can’t relate to each other.