Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have created a matrix table below (sorry that I have to cover the data). I would like to include the following new columns into the matrix table at the end of the table. (Entity = Subsidiary)
- Total Entity = Entity 1 + Entity 2 + Entity 3
- Adj Entity
- Total all = Total Entity + Adj Entity
** Adj Entity currently is also one of the subsidiary in the data set.
So the end result will be like
Basically I would like to create new "calculated column" that derived from the columns from the matrix. Also I cannot create another measure and drop it into visual - Values, cause it will create 2 amounts for each entities. I just need the 3 columns at the end of the matrix.
I thought of duplicate the same fact table, change the subsidiary value to Total Entity (exclude Adj Entity) and append the table to the existing table. This will fake the "Total Entity" as one of the subsidiary therefore archieve the result that I want. However, I do not really wish to do this, as the appended fact table will be very big which consume the space / memory.
Hope to seek some helps here. Thank you.
Solved! Go to Solution.
Hi @TK_FPA ,
There is no more elegant way to implement your requirement, you can refer to this post below to see if the solution can be used as an alternative...
First, you can create three measures as below:
Measure1 =
IF (
ISINSCOPE ( 'Table'[Financial Year] ) || ISINSCOPE ( 'Table'[Subsidiary] ),
BLANK (),
[Total Entity]
)
Measure2 =
IF (
ISINSCOPE ( 'Table'[Financial Year] ) || ISINSCOPE ( 'Table'[Subsidiary] ),
BLANK (),
[Adj Entity]
)
Measure3 =
IF (
ISINSCOPE ( 'Table'[Financial Year] ) || ISINSCOPE ( 'Table'[Subsidiary] ),
SUM ( 'Table'[Actual] ),
[Total all]
)
Then follow the steps in the link below:
Matrix visual - Measure to be shown only in total's part of visual
A workaround would be:
1. Select the Values fields(Measure1, Measure2 and Measure3)you want to hide.
2. In the visualization menu, go to Format--> Column headers and set both Auto-size column width and Word wrap to Off
3. In the visual, reduce the width of the column until it disappears completely. This way it will be still there, but completely hidden.
Best Regards
Hi @TK_FPA ,
In addition, please review the following blog and check if it can achieve your requirement.
How to put the row and column subtotals in front of the matrix
Best Regards
Hi @TK_FPA ,
There is no more elegant way to implement your requirement, you can refer to this post below to see if the solution can be used as an alternative...
First, you can create three measures as below:
Measure1 =
IF (
ISINSCOPE ( 'Table'[Financial Year] ) || ISINSCOPE ( 'Table'[Subsidiary] ),
BLANK (),
[Total Entity]
)
Measure2 =
IF (
ISINSCOPE ( 'Table'[Financial Year] ) || ISINSCOPE ( 'Table'[Subsidiary] ),
BLANK (),
[Adj Entity]
)
Measure3 =
IF (
ISINSCOPE ( 'Table'[Financial Year] ) || ISINSCOPE ( 'Table'[Subsidiary] ),
SUM ( 'Table'[Actual] ),
[Total all]
)
Then follow the steps in the link below:
Matrix visual - Measure to be shown only in total's part of visual
A workaround would be:
1. Select the Values fields(Measure1, Measure2 and Measure3)you want to hide.
2. In the visualization menu, go to Format--> Column headers and set both Auto-size column width and Word wrap to Off
3. In the visual, reduce the width of the column until it disappears completely. This way it will be still there, but completely hidden.
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |