Forum Discussion
DAX for Input Output Analysis
Dear all
I am embarking on a project to construct an input-output matrix encompassing several regions and economic sectors. To illustrate, let’s consider 3 regions and 4 sectors. This will result in a 12 x 12 square matrix, where each cell’s value indicates the amount of output from one sector (as denoted by the column) required as input by another sector (as represented by the row).
| reg A | reg A | reg A | reg A | reg B | reg B | reg B | reg B | reg C | reg C | reg C | reg C | ||
| sec 1 | sec 2 | sec 3 | sec 4 | sec 1 | sec 2 | sec 3 | sec 4 | sec 1 | sec 2 | sec 3 | sec 4 | ||
reg A | sec 1 | 4 | 2 | 6 | 6 | 1 | 3 | 6 | 8 | 3 | 5 | 7 | 7 |
| reg A | sec 2 | 6 | 8 | 3 | 6 | 3 | 6 | 6 | 3 | 9 | 3 | 2 | 5 |
| reg A | sec 3 | 5 | 3 | 4 | 7 | 8 | 2 | 5 | 9 | 9 | 9 | 4 | 8 |
| reg A | sec 4 | 7 | 2 | 1 | 5 | 6 | 8 | 5 | 3 | 8 | 6 | 1 | 4 |
| reg B | sec 1 | 6 | 4 | 4 | 6 | 1 | 1 | 8 | 8 | 3 | 4 | 9 | 2 |
| reg B | sec 2 | 7 | 4 | 8 | 3 | 2 | 7 | 1 | 3 | 4 | 6 | 1 | 5 |
| reg B | sec 3 | 9 | 2 | 2 | 1 | 6 | 1 | 6 | 7 | 2 | 1 | 7 | 9 |
| reg B | sec 4 | 6 | 3 | 5 | 2 | 3 | 5 | 2 | 8 | 5 | 1 | 4 | 8 |
| reg C | sec 1 | 2 | 7 | 2 | 8 | 1 | 2 | 5 | 5 | 6 | 9 | 6 | 2 |
| reg C | sec 2 | 3 | 4 | 2 | 2 | 6 | 3 | 8 | 4 | 6 | 2 | 7 | 2 |
| reg C | sec 3 | 2 | 8 | 9 | 9 | 5 | 3 | 5 | 6 | 1 | 4 | 1 | 5 |
| reg C | sec 4 | 8 | 5 | 4 | 8 | 1 | 7 | 8 | 7 | 6 | 8 | 9 | 3 |
The question :
1. How should we structure the data and what measurements and queries are necessary to accurately capture the input-output dynamics across these sectors and regions?
2. The aim is to synthesize an aggregate table. For instance, I intend to combine regions A and B into a new entity named "non C", and sum sectors 1, 2, and 3 to create a category "other than 4". This will enable us to condense the information into a more manageable 4 x 4 matrix.
Please help
Oka
1 Reply
Hi angsoka
You should be able to achieve the 12x 12 matrix with a dataset with four columns:
Region Sector Input OutputYou can then create a 5th column on the table using DAX with the logic:
Not Sector 4 Calc = IF( Table[Sector] = "sec 4", Table[Sector], "Other than 4 )You can use similar logic for the "Non C" calculation on regions. Then add both of these new columns to a matrix visual in Power BI, with calculations to sum the input and output.
