Matrix with Categorical Data to look live values

[MattiaFratello]

Hi everyone, 

Is it possible in Power BI or Report Builder to create a matrix as the following?

To have Category as columns and under each category a list of games (present in that category) ordered by GGR.

I believe no but maybe someone has find a solution.

Thanks in advance.

[MFelix]

Hi @MattiaFratello ,

You can have this type of matrix however there would be an additional row on the headers for each catory something similar to:

Category A		Category B		Category C
Game	GGR	Game	GGR	Game	GGR
Game 1	GGR 1	Game 1	GGR 1	Game 1	GGR 1
Game 2	GGR 2	Game 2	GGR 2	Game 2	GGR 2

The only question I have is concerning what you present on the GGR you want all the games to be sorted based on the GGR?

So for category A the first game would be the one with the highest GGR and so on?

This can be achieve with some dax coding and an additional setup of the calculations.

If you confirm my conclusion on the sorting I can try and send you a example.

[MattiaFratello]

Hi @MFelix, that's exactly what I need. 

Would be lovely to have an example. Thanks in advance!

[MFelix · Accepted solution]

HI @MattiaFratello ,

Not sure if the setup is the same I have but you need something similar to this.

• Create a table for the Matrix headers this should include the Categories names and a colum for Game and GRR

• Also create a parameter table with numeric range (in my case I created until 20 but it can be more.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if#create-a-parameter

Now add the following measure:

Matrix Values = 
        VAR _temptable = CALCULATETABLE(
			'Games Table',
			'Games Table'[Category] IN VALUES(Categories[Category])
		    )

		VAR _TopN = TOPN(
			[Order Value],
			_temptable,
			'Games Table'[GGR]
		    )

		RETURN
			IF(
				[Order Value] <= COUNTROWS(_temptable),
				SWITCH(
					SELECTEDVALUE('Categories'[Calculation]),
					"Game", MAXX(
						TOPN(
							1,
							_TopN
						),
						'Games Table'[Game Name]
					),
					"GRR", MAXX(
						TOPN(
							1,
							_TopN
						),
						'Games Table'[GGR]
					)
				)
			)

Now setup you matrix with the following way:

• Rows:
  • Order
• Columns
  • Category
  • Calculation
• Values
  • Matrix Value

Please see file attach.

[MattiaFratello]

Hi @MFelix, is there a way to let this work if I have a Fact Table with all my metrics, then two Dim Tables, one for Games and one for Categories (everything with relationship 1 -> n)

Fact n -> 1 Games 
Games n -> 1 Categories

Thanks in advance

[MFelix]

Hi @MattiaFratello ,

Yes there is no problem you just need to replace the Categories and the Games on the DAX  measure by the columns on the dimension tables

Would be similar to this:

Matrix Values = 
        VAR _temptable = FILTER(
			ADDCOLUMNS('Fact Table',"Category", DimTable[Category], "Game", GameTable[Game]),
			[Category] IN VALUES(Categories[Category])
		    )

		VAR _TopN = TOPN(
			[Order Value],
			_temptable,
			'Fact Table'[GGR]
		    )

		RETURN
			IF(
				[Order Value] <= COUNTROWS(_temptable),
				SWITCH(
					SELECTEDVALUE('Categories'[Calculation]),
					"Game", MAXX(
						TOPN(
							1,
							_TopN
						),
						'[Game]
					),
					"GRR", MAXX(
						TOPN(
							1,
							_TopN
						),
						[GGR]
					)
				)
			)

Doing the changes on the fly so they may need some updates.

[MattiaFratello]

@MFelix Thanks a lot mate!

[MattiaFratello]

Hi @MFelix, I've updated your formula as follow, and it seems to work mostly fine:

Matrix Values = 
    VAR _temptable = FILTER(
        ADDCOLUMNS('Casino Detailed - Current', 
            "Category", RELATED('Game Categories'[Game Category]), 
            "Game", RELATED(Games[Game])
        ),
        [Category] IN VALUES(Categories[Game Category])
    )

    VAR _TopN = TOPN(
        [Order Value],
        _temptable,
        [GGR]
    )

    RETURN
        IF(
            [Order Value] <= COUNTROWS(_temptable),
            SWITCH(
                SELECTEDVALUE('Categories'[Calculation]),
                "Game", MAXX(
                    TOPN(
                        1,
                        _TopN
                    ),
                    [Game]  
                ),
                "GGR", MAXX(  
                    TOPN(
                        1,
                        _TopN
                    ),
                    [GGR]
                )
            )
        )

My only question is, I have several slicers such as Date, Operator, Provider.

It seems that they are duplicating some rows withing the Matrix.

Any ideas?

Thanks in advance

[MFelix]

Hi @MattiaFratello ,

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.