Using values from another table in a measure

[Anonymous]

Hello,

I need to use data from two tabs to create a radar chart using measure.

Those are my two tables:

Table 1:

Domain Activity | Actvity  | Actual Level 

Mining               | Iron      |  0

Mining               | Coper   |  2

Crafting             | Belt       |  1

Crafting             | Helm     |  1

Crafting             | Sword   |  2

Lumbering         | Oak      |  0

Table 2:

Activity Familly  | Actvity   | Levels

Mining               | Iron      |  0

Mining               | Iron      |  1

Mining               | Iron      |  2

Mining               | Iron      |  3

Mining               | Coper   |  0

Mining               | Coper   |  1

Mining               |  Coper   |  2

Crafting             | Belt       |  0

Crafting             | Belt       |  1

Crafting             | Belt       |  2

Crafting             | Helm     |  0

Crafting             | Helm     |  1

Crafting             | Sword   |  0

Crafting             | Sword   |  1

Crafting             | Sword   |  2

Lumbering         | Oak      |  0

Lumbering         | Oak      |  1

I want to create a radar chart with the Domain Activity as categorie and as axis a measur to have a ratio of the max level:

Mining woul be (0 + 2)/(3 + 2) = 2/5 so 40%

Crafting should display (1 + 1 + 2)/(2 + 1 + 2) so 80%

And Lumbering 0/1 so 100%

What measure should I use? I can't get it to work since the datas are not in the same table, how can I do?

BR

Bastien B

[v-lili6-msft · Accepted solution]

HI, @Anonymous

After my test, you could do these follow my steps as below:

Step1:

Duplicate the table2

Step2:

Group By Duplicate table2 as below:

Step3:

Merge table 1 with duplicate table

then expand the column

Step4:

Create this measure

Measure = DIVIDE(CALCULATE(SUM(Table1[Actual Level])),CALCULATE(SUM(Table1[Table2 (2).Max])))

Result:

here is pbix, please try it.

https://www.dropbox.com/s/fu2w17dzhpgarx9/Using%20values%20from%20another%20table%20in%20a%20measure.pbix?dl=0

Best Regards,

Lin

[LivioLanzo]

@Anonymous

why do you not perform a union of the two tables?

[Anonymous]

Both table have many more columns that are only prensent in each and I don't know how to do it too 😞

[LivioLanzo]

It is easy to combine tables with Power Query

https://www.contextures.com/excelpowerquerycombinetables.html

[Anonymous]

I would like to do it without merging since my report has other element and it will modify them 😕

[LivioLanzo]

you can create union table of the matching ID columns and at the same time keep your original tables