Forum Discussion
use data from two different excel tabs
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
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]) * 100Please 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.
1 Reply
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]) * 100Please 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.