How do I calculate something based on values in two different tables?

[dbltnk]

Hello again,

I'm still working on analytics data from a two-player strategy game.

I have two different, connected tables (connected via an ID). One table knows how often a specific unit was produced. The other table knows how many units are in one production set. 

https://www.dropbox.com/s/anf4kjvtq667b65/rep2.png?dl=0

Now I want to calculate for each unit type how many sets were produced. But since both values are in different tables I cannot just create a measure or new column. 

How can I solve this?

dbltnk

[Eric_Zhang]

---

@dbltnk wrote:

Hello again,

I'm still working on analytics data from a two-player strategy game.

I have two different, connected tables (connected via an ID). One table knows how often a specific unit was produced. The other table knows how many units are in one production set. 

https://www.dropbox.com/s/anf4kjvtq667b65/rep2.png?dl=0

Now I want to calculate for each unit type how many sets were produced. But since both values are in different tables I cannot just create a measure or new column. 

How can I solve this?

dbltnk

---

@dbltnk

I‘d guess that those two table have some columns to link each other? What are the column(s)? Could you please post some sample data of those tables?

[dbltnk]

@Eric_Zhang

Sure thing. You can see them in the relationship editor:

https://www.dropbox.com/s/e4qzraem7tns5jp/h3.png?dl=0

[Anonymous]

It's not wildly super clear what you got going on here (to me), but my best guess is:

Total Units = COUNTROWS(logic_production_unit)
Total Sets = SUMX(Units_v025, CALCULATE(DIVIDE([Total Units], Units_v025[Units Per Set])))

This assume each individual unit created got a row in logic_product_unit and Units_v025 is just a lookup table that has unit info... including the "Units Per Set" column.

[dbltnk]

Hi @Anonymous - thanks for your suggestion!

Sorry for not being clear with my question. But I spent another 90 minutes with your suggestion and the DAX documentation today and still did not come to any useful result. I'll try to make it more clear this time:

The first table (logic_production_unit) is basically a looooong list of events with a timestamp. Each of this events tell me when a specific unit was built. Here in the example you can see that three units of type "H_Recruit" were built at the same time stamp:

https://www.dropbox.com/s/yjggp27yvws6cmn/s1.png?dl=0

The second table (Units_v025) has a lot of information about the different unit types. As you can see here in the example it tells me that "H_Recruit" has a "Group Size" value of three.

https://www.dropbox.com/s/sdbx19nx3lk3iul/s2.png?dl=0

Now in my analysis I have a table where I count all occurences of the "created a H_Recruit unit" event from the first table. And I can output the "Group Size" for that unit type next to it since both tables are connected (via logic_production_unit[descid] and Units_v025[unit_id]).

https://www.dropbox.com/s/anf4kjvtq667b65/rep2.png?dl=0

Now what I want to do is to divide the amount of units produced of a certain type (in the example that would be 22764 units of type H_Recruit) and divide that number by the "Group Size" of that type (3). But I just can't figure out what formula to use for this.

[Anonymous]

I still feel pretty good about my questions, maybe modulo a call to RELATED:

Total Units = COUNTROWS(logic_production_unit)
Total Sets = SUMX(Units_v025, CALCULATE(DIVIDE([Total Units], RELATED(Units_v025[Units Per Set]))))

What kind of "no correct" do we have going on with these?

[dbltnk]

Thanks again, @Anonymous. I created a measure for Total Units and a column for Total Sets and in the end get that error message:

"Something's wrong with one or more fields: (logic_production_unit) Sets_Produced: The column 'Units_v025[Group Size]' either doesn't exist or doesn't have a relationship to any table available in the current context."

There's no typo and (as you can see above) both tables have a working relationship. What could still be wrong here?

[Anonymous]

The thinking with Total Sets was that it too would be a measure.  If you want a calc column there, we would need to tweak it a bit.

Errr, which table were you putting in on?

[Anonymous · Accepted solution]

Ya, apologies for screwing these up -- either of these should get you going 🙂

Total Sets 1 = SUMX(logic_product_unit, DIVIDE(1, RELATED(Units_v025[Units Per Set])))

Total Sets 2 = SUMX(Units_v025, DIVIDE(COUNTROWS(RELATEDTABLE(logic_production_unit)), Units_v025[Units Per Set]))

[dbltnk]

Thanks again @Anonymous - that worked like a charm! =D