Matrix Step Style Report

[Anonymous]

Hello,

I have the following resulting data (simplified from multiplied related tables for illustration)

Category	Department	Code	Inv Level	Description
Food	Frozen	FF01	Red	Frozen Peas
Food	Frozen	FF02	Green	Frozen Brussel Sprouts
Food	Meat	ME01	Red	Chicken Thighs
Food	Meat	ME02	Green	Chicken Legs
Cereal	Generic	GEC01	Green	Toasty Pops
Cereal	Brand	KG01	Red	Toasted Flakes

The ask I've been given is to create a more "non-repeated" view in Power BI, so they (on the top part of the page) can see this data and on the other half seeing "normal" sales and other data that i've been able to visualise. They are hoping to see something like this (note red/green will just be handled via the cell formating formulas, but it's easier to leave words here). The reason we are keeping the Item Code here is that we use that in the graphs as the legend would get too crazy otherwise 😉

Category	Department	Item Code	Inventory Level	Description
Food	Frozen	FF01	Red	Frozen Peas
		FF02	Green	Frozen Brussel Sprouts
	Meat	ME01	Red	Chicken Thighs
		ME02	Green	Chicken Legs
Cereal	Generic	GEC01	Green	Toasty Pops
	Brand	KG01	Red	Toasted Flakes

Initially, I was thinking that I could use a Matrix view, but that took up too much real estate as it was a tree-view. I thought of some weird power-m solutions that I could use to handle this in a standard table, but I was hoping there was either something I missed with the matrix, if there was another visualisation in the marketplace, or anyone had ideas. 

Thanks in advance for the help

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 columns.

cate =
VAR _1 =
    RANKX (
        FILTER ( 'Table', 'Table'[Category] = EARLIER ( 'Table'[Category] ) ),
        'Table'[Index],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( _1 = 1, 'Table'[Category], BLANK () )

depa =
VAR _1 =
    RANKX (
        FILTER (
            'Table',
            'Table'[Category] = EARLIER ( 'Table'[Category] )
                && 'Table'[Department] = EARLIER ( 'Table'[Department] )
        ),
        'Table'[Index],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( _1 = 1, 'Table'[Department], BLANK () )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.