use data from two different excel tabs

[JPS_SEQ]

Hi,

I've a excel database with two tabs:

Tab 1:

Book	Type	Team	Month
A	Urgent	A	January
B	Normal	A	January
A	Normal	B	January
D	Urgent	D	February
F	Normal	C	March
E	Normal	B	March
E	Normal	A	April
G	Urgent	D	May
H	Urgent	C	May
H	Normal	B	May

Tab 2

Book	Number of pages
A	80
B	40
C	70
D	5
E	20
F	30
G	15

I 'd like to create a table with the ratio of "type" per "100 pages" with the following configuration:

Type: Normal
Team	January	February	March	April	May
A
B
C
D

For instance:

January should be calculated as:  

   there are two books in january: book A and B. Total of pages: 80 +40 = 120 pages

   there was one "normal" ocurrence for team A in january. 

   Thus, the ratio for team A in January is:   1/120*100 

[Anonymous · Accepted solution]

Hi @JPS_SEQ ,

In order to make it easier to sort by month name, I have added a column of MonthNo to the sample data table, please don't care.

First please create a calculated table using the following DAX:

Table = 
SUMMARIZE(
    'Table 1',
    'Table 1'[Month],
    'Table 1'[MonthNo],
    "TotalPages",
    SUMX(RELATEDTABLE('Table 2'), 'Table 2'[Number of pages])
)

Then use this DAX to create a measure:

Total of pages = 
VAR _Normal =
CALCULATE(
    COUNTROWS('Table 1'),
    'Table 1'[Type] = "Normal"
)
RETURN
_Normal / MAX('Table'[TotalPages]) * 100

Please set the percent sign form and the number of decimal places to be retained in the result of this measure.
Create the visual like this and the final output is as below:

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