Forum Discussion

ashishoza12345's avatar
4 years ago
Solved

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

  • PaulDBrown's avatar
    PaulDBrown
    Community 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 

     

     

    • ashishoza12345's avatar
      ashishoza12345
      Helper 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!

      • PaulDBrown's avatar
        PaulDBrown
        Community 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

         

  • 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

  • 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.

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion

      "when I try to hide any of the blank column, the whole matrix gets disorganised"

       

      Can you show a depiction?