Forum Discussion
How to show different data cells in same matrix table
Hi everyone! I wanted to find out a way to show 3 different types of data rows - X, Y and Z in the same matrix table (doesn't have to be a matrix table, it can be any table).
Z = X/Y for each column in the table. I have to calculate this. Both X and Y already exist in the dataset.
Here is variable "X", which is under column "rollup_level_6_name" in the dataset, Append1.
Table 1.
Here is variable "Y", which is in the column "rollup_level_4_name" in the dataset, Append1.
Table 2.
Here is how I am currently calculating all of the different columns in the above table -
Table 1. And then in the filter's pane of the table, selecting, "X" in rollup_level_6_name.
Table 2. And then in the filter's pane of the table, selecting, "Y" in rollup_level_4_name.
The table has to have the following headings (also see Table 1 and Table 2 for Power BI output) -
X/Y/Z | Sep-23 Actual | Sep-23 Budget | Sep-22 Actual | Variance | Variance
I have measures to calculate each column -
Table 4. CY YTD Actual =
CALCULATE(
SUM('Append1'[ytdamount]),
'Append1'[scenario] = "Actual",
TREATAS(
VALUES('Calendar'[Date]),
'Append1'[Date]
)
)
Table 4. CY YTD Budget =
CALCULATE(
SUM('Append1'[ytdamount]),
CONTAINSSTRING('Append1'[scenario], "B"),
TREATAS(
VALUES('Calendar'[Date]),
'Append1'[Date]
)
)
Table 4. PY YTD Actual =
CALCULATE(
[1a. Current Quarter Actual],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
Table 4. Variance A v. B = 'Append1'[Table 4. CY YTD Actual] - 'Append1'[Table 4. CY YTD Budget]
Table 4. Variance % A v. B = 'Append1'[Table 4. Variance A v. B] / 'Append1'[Table 4. CY YTD Budget]
Table 4. Variance CY v. PY = 'Append1'[Table 4. CY YTD Actual]-'Append1'[Table 4. PY YTD Actual]
Table 4. Variance % CY v. PY = 'Append1'[Table 4. Variance CY v. PY]/'Append1'[Table 4. PY YTD Actual]
Problem is - I can create separate tables to show each row, but having it in 1 table will be a lot better. Data is pulling from another software. Dataset is scrubbed and shrunk to relevant columns. Any ideas? I've been at this for a week...
| rollup_level_1_name | rollup_level_2_name | rollup_level_3_name | rollup_level_4_name | rollup_level_5_name | rollup_level_6_name | scenario | fiscalyear | Quarter | fiscalmonth | monthname | ytdamount |
| B1 | A2 | A3 | A | A5 | X | Actual | 2023 | Q3 | 9 | 01-Sep | 1 |
| B1 | A2 | A3 | A | A5 | X | Actual | 2023 | Q3 | 9 | 01-Sep | 2 |
| B1 | A2 | A3 | C | C2 | C3 | Budget | 2023 | Q3 | 9 | 01-Sep | 3 |
| B1 | A2 | A3 | D | D1 | D2 | Actual | 2023 | Q3 | 9 | 01-Sep | 4 |
| B1 | B2 | B3 | Y | NaN | NaN | Actual | 2023 | Q3 | 9 | 01-Sep | 5 |
| B1 | B2 | B3 | Y2 | NaN | NaN | Actual | 2023 | Q3 | 9 | 01-Sep | 5 |
| B1 | B2 | B3 | Y3 | NaN | NaN | Actual | 2023 | Q3 | 9 | 01-Sep | 5 |
1 Reply
- AnonymousNot applicable
Hi powerbinewbie23 ,
Can't it be displayed in a table visual object? What do you want the output to look like? Please provide more details with your desired output and pbix file without privacy information (or some sample data) .
How to Get Your Question Answered Quickly - Microsoft Fabric Community
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.