Create calculations with unrelated tables

[Syndicate_Admin]

Good morning.

I'm writing to you because I need help with Power BI.

I have a model with many tables but for the calculation I need 3 are involved:

The first is a table of properties where the ID and property name fields are located.

The second, related to the first from 1 to many, contains visitor data. This is a table created by joining two other tables and has calculated fields such as the number of visitors to each property, the property ID, date, zone, access, etc

The third table that is related to the first also from 1 to many and contains data of each property such as ID, consumption, date, etc.

With all this, I need to create a calculation of consumption per visitor but when I create it, since they are not related, the measures I create do not come out with correct data. I have also tried creating an intermediate table that contains the fields I need from tables 2 and 3 but it does not come out correct either.

How can I do it?

Thank you very much in advance

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

Based on your description, I have created the following model which allows me to calculate the consumption per visitor by using the following formula.

ConsumptionPerVisitor = DIVIDE([TotalConsumption], SUM(Visitors[TotalVisitors]), 0)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Syndicate_Admin , with common tables like property id, date and another you can analyze data for a measure at the total level or common dimension level 

m1= divide(Sum(Table3[consumption]), countrows(Table2))