Forum Discussion
Create new fields in a matrix
Hi Folks,
I need your help in creating fields as shown below:
(1) Indoor games filed is inclusive of Carom, Chess and TT
(2) Sum of Indoor games field for the financial year 2020-2021
(3) Change field is the difference between indoor games held between 2019-2020 and 2020-2021
(4) Total of Statewide, Metropolitian and regional as shown below:
Given below is the link to access the dataset & PBIx file:
https://drive.google.com/drive/folders/1OrJyuBL7TteasL5whEWVWlM7rB5wAZ85?usp=sharing
Thanks in advance!
Will the financial year comparison be static or does it need to be dynamic?
Meanwhile, this is the closest I've got.
1) Create a dimension table for Area and Period using:
Dim Area = ADDCOLUMNS ( SUMMARIZE ( 'Game Stats', 'Game Stats'[Area], 'Game Stats'[Region] ), "Location", IF ( 'Game Stats'[Region] = "Regional / Rural", "Regional", "Metropolitan" ), "StateWide", "Statewide" )Dim Period = ADDCOLUMNS ( DISTINCT ( 'Game Stats'[Financial Year] ), "Order", RANKX ( VALUES ( 'Game Stats'[Financial Year] ), 'Game Stats'[Financial Year], , ASC, DENSE ) )2) create the structure you need for the matrix columns using:
Column Structure = VAR _Period = VALUES ( 'Game Stats'[Financial Year] ) VAR _Games = ADDCOLUMNS ( VALUES ( 'Game Stats'[Game] ), "Order", RANKX ( VALUES ( 'Game Stats'[Game] ), 'Game Stats'[Game],, ASC, DENSE ) + 1 ) VAR _Totals = { ( "Total Indoor", 1 ) } VAR _Change = { ( "Change", 100 ) } VAR _Base = UNION ( _Totals, _Games, _Change ) RETURN CROSSJOIN ( _Base, _Period )Set up the relationships between these tables and the main table using the corresponding fields:
Create a simple sum measure for records
Sum Records = SUM('Game Stats'[Records])and the final measure for the matrix:
Sum Matrix Columns = VAR _Game = CALCULATE ( [Sum Records], TREATAS ( VALUES ( 'Column Structure'[Game] ), 'Game Stats'[Game] ) ) VAR _indoor = CALCULATE ( [Sum Records], 'Game Stats'[Game Type] = "Indoor" ) VAR _Change = CALCULATE ( [Sum Records], REMOVEFILTERS ( 'Dim Period'[dFinancialYear] ), 'Dim Period'[Order] = 2, 'Game Stats'[Game Type] = "Indoor" ) - CALCULATE ( [Sum Records], REMOVEFILTERS ( 'Dim Period'[dFinancialYear] ), 'Dim Period'[Order] = 1, 'Game Stats'[Game Type] = "Indoor" ) RETURN IF ( ISINSCOPE ( 'Dim Area'[StateWide] ), SWITCH ( SELECTEDVALUE ( 'Column Structure'[Order] ), 1, _indoor, 100, IF ( SELECTEDVALUE ( 'Dim Period'[Order] ) = 2, _Change, BLANK () ), IF ( SELECTEDVALUE ( 'Dim Period'[Order] ) < 2, _Game ) ) )Create the matrix using the fields from the Dim Area table, the Dim Period table and the Column Structure table, and add the [Sum Matrix Coumns] measure. Tweak remove/format the totals to get:
I've attached the sample PBIX file
9 Replies
- PaulDBrownCommunity Champion
Will the financial year comparison be static or does it need to be dynamic?
Meanwhile, this is the closest I've got.
1) Create a dimension table for Area and Period using:
Dim Area = ADDCOLUMNS ( SUMMARIZE ( 'Game Stats', 'Game Stats'[Area], 'Game Stats'[Region] ), "Location", IF ( 'Game Stats'[Region] = "Regional / Rural", "Regional", "Metropolitan" ), "StateWide", "Statewide" )Dim Period = ADDCOLUMNS ( DISTINCT ( 'Game Stats'[Financial Year] ), "Order", RANKX ( VALUES ( 'Game Stats'[Financial Year] ), 'Game Stats'[Financial Year], , ASC, DENSE ) )2) create the structure you need for the matrix columns using:
Column Structure = VAR _Period = VALUES ( 'Game Stats'[Financial Year] ) VAR _Games = ADDCOLUMNS ( VALUES ( 'Game Stats'[Game] ), "Order", RANKX ( VALUES ( 'Game Stats'[Game] ), 'Game Stats'[Game],, ASC, DENSE ) + 1 ) VAR _Totals = { ( "Total Indoor", 1 ) } VAR _Change = { ( "Change", 100 ) } VAR _Base = UNION ( _Totals, _Games, _Change ) RETURN CROSSJOIN ( _Base, _Period )Set up the relationships between these tables and the main table using the corresponding fields:
Create a simple sum measure for records
Sum Records = SUM('Game Stats'[Records])and the final measure for the matrix:
Sum Matrix Columns = VAR _Game = CALCULATE ( [Sum Records], TREATAS ( VALUES ( 'Column Structure'[Game] ), 'Game Stats'[Game] ) ) VAR _indoor = CALCULATE ( [Sum Records], 'Game Stats'[Game Type] = "Indoor" ) VAR _Change = CALCULATE ( [Sum Records], REMOVEFILTERS ( 'Dim Period'[dFinancialYear] ), 'Dim Period'[Order] = 2, 'Game Stats'[Game Type] = "Indoor" ) - CALCULATE ( [Sum Records], REMOVEFILTERS ( 'Dim Period'[dFinancialYear] ), 'Dim Period'[Order] = 1, 'Game Stats'[Game Type] = "Indoor" ) RETURN IF ( ISINSCOPE ( 'Dim Area'[StateWide] ), SWITCH ( SELECTEDVALUE ( 'Column Structure'[Order] ), 1, _indoor, 100, IF ( SELECTEDVALUE ( 'Dim Period'[Order] ) = 2, _Change, BLANK () ), IF ( SELECTEDVALUE ( 'Dim Period'[Order] ) < 2, _Game ) ) )Create the matrix using the fields from the Dim Area table, the Dim Period table and the Column Structure table, and add the [Sum Matrix Coumns] measure. Tweak remove/format the totals to get:
I've attached the sample PBIX file
- ashishoza12345Helper I
Hi PaulDBrown ,
Thank you so much for your help.
I need one more help on this:(1) How can I re-arrange 2021-2022 on the left hand side and 2019-2021 on the right hand side.
Basically swap the years.(2) I want to see the databars for "Total Indoor" based on the percentage of each record divided by total record for that year 2019-2021 and same databars for change field.
Your help will be highly appreciated!
Thank you in advance!
- PaulDBrownCommunity Champion
I'm afraid that the method posted has very little flexibility with regards to conditional formatting since the values all come from one measure. A little can be done, but not what you are after:
The good news is that there is another method which will allow for CF. Basically it entails using the recently included field parameters functionality, althoug there are some tradeoffs.
Firstly, delete the 'Matrix Columns' table.
To change the order of the financial periods in the matrix, you need to change the order value in the Dim Period table:
Sort the table by the Order fields in ascending order; then sort the dFinancial Year column by the Order column
Now create the measures you need.
Total Indoor = CALCULATE([Sum Records], 'Game Stats'[Game Type] = "Indoor")% Indoor = VAR _TotalIndoor = CALCULATE([Total Indoor], ALL('Dim Area'), 'Game Stats'[Game Type] = "Indoor") RETURN IF( MAX('Dim Period'[Order]) = 2, DIVIDE([Total Indoor], _TotalIndoor))Change = VAR _Change = CALCULATE ( [Sum Records], REMOVEFILTERS ( 'Dim Period'[dFinancial Year] ), 'Dim Period'[Order] = 1, 'Game Stats'[Game Type] = "Indoor" ) - CALCULATE ( [Sum Records], REMOVEFILTERS ( 'Dim Period'[dFinancial Year] ), 'Dim Period'[Order] = 2, 'Game Stats'[Game Type] = "Indoor" ) RETURN IF ( MAX ( 'Dim Period'[Order] ) = 1, _Change )Carrom = VAR _Game = CALCULATE([Sum Records], 'Game Stats'[Game] = "Carrom") RETURN IF(MAX('Dim Period'[Order]) = 2, _Game)Chess = VAR _Game = CALCULATE([Sum Records], 'Game Stats'[Game] = "Chess") RETURN IF(MAX('Dim Period'[Order]) = 2, _Game)Cricket = VAR _Game = CALCULATE([Sum Records], 'Game Stats'[Game] = "Cricket") RETURN IF(MAX('Dim Period'[Order]) = 2, _Game)TT = VAR _Game = CALCULATE([Sum Records], 'Game Stats'[Game] = "TT") RETURN IF(MAX('Dim Period'[Order]) = 2, _Game)Now create the Matrix column table using:
Matrix = VAR _periods = VALUES ( 'Game Stats'[Financial Year] ) VAR FP = { ( "Total Indoor", NAMEOF ( 'Field Parameters'[Total Indoor] ), 0 ), ( "% Indoor", NAMEOF ( 'Field Parameters'[% Indoor] ), 1 ), ( "Change", NAMEOF ( 'Field Parameters'[Change] ), 2 ), ( "Carrom", NAMEOF ( 'Field Parameters'[Carrom] ), 3 ), ( "Chess", NAMEOF ( 'Field Parameters'[Chess] ), 4 ), ( "Cricket", NAMEOF ( 'Field Parameters'[Cricket] ), 5 ), ( "TT", NAMEOF ( 'Field Parameters'[TT] ), 6 ) } RETURN CALCULATETABLE ( CROSSJOIN ( FP, _periods ) )Create the relationship between the Dim Period table and the Matrix table:
Set up the matrix with the Dim Area fields for rows, the dFinancial Year from Dim Period & the Matrix column, and add the measures in order to get this:
Unfortunately there are blanks fields (the measures themselves set these blanks) for "Columns" which you do not wish to have. To get rid of them, turn off Word Wrap for Column Headers and values:
Now you can hide the rogue columns by draggin the column boundary:
Since the values are all individual measures, you can now apply the conditional formatting as you wish:
I've attached the sample PBIX file
- ashishoza12345Helper I
Hi PaulDBrown ,
Thanks you so much for the solution. Really Appreciated.
Yes, I tried using it but still I have a small doubt.I will come back to you asap. 🙂
Kind Regards
Ashish
- ashishoza12345Helper I
Hi PaulDBrown ,
Thanks heaps for your help.
Can You please let me know if we can hide the columns which contains blank value for example: Carrom, Chess, Cricket, TT using any measure on a visual level filter.
The reason why I would like to use this is because my column header values in the matrix is more than 20 characters and when I try to hide any of the blank column, the whole matrix gets disorganised.- PaulDBrownCommunity Champion
"when I try to hide any of the blank column, the whole matrix gets disorganised"
Can you show a depiction?