Doble row Column Header

[LP2803]

Hi Team,

I have a car sales data in my powerbi like the below picture 1 and all i would like is to recreate a percentile table like the picture 2 below.  Kindly help me how can i achieve this result.

Picture 1:

Picture 2:

I have the percentiles calculated in a measure. But unable to get the name of the percentile in the column value (like the highted in picture 2)

Please help me.

[Icey · Accepted solution]

Hi @LP2803 ,

If you want to achieve the effect of your Picture 2, you should create another table, as shown below. This table was created with Enter Data. The sort column is intended to sort the Infor columns in the specified order.

After you've created the new table, you can write your measures like this.

measure =
IF (
    MAX ( 'Table (2)'[Infor] ) = "Sales Amount",
    SWITCH (
        MAX ( 'Table (2)'[percentile] ),
        "25th percentile", ( PERCENTILE.INC ( 'Table'[Sales Amount], 0.25 ) ),
        "50th percentile", ( PERCENTILE.INC ( 'Table'[Sales Amount], 0.5 ) ),
        "75th percentile", ( PERCENTILE.INC ( 'Table'[Sales Amount], 0.7 ) )
    ),
    SWITCH (
        MAX ( 'Table (2)'[percentile] ),
        "25th percentile", ( PERCENTILE.INC ( 'Table'[Order quantity], 0.25 ) ),
        "50th percentile", ( PERCENTILE.INC ( 'Table'[Order quantity], 0.5 ) ),
        "75th percentile", ( PERCENTILE.INC ( 'Table'[Order quantity], 0.7 ) )
    )
)

Then you can create a matrix view, as shown below. Be careful not to reverse the order of the columns.

Click the button in the red box and the display is as below.

If you don't want to see the Total value, just turn it off in Subtotals under Format. The result is as below.

You can check more details from here.

Best Regards,

Icey

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

[Anand24]

Hi @LP2803 ,

Measures can't be pulled in the column field of matrix visualization but columns can.

Here is what you can do:

1. Create a new column with same formulae/calculations as that of your measure and pull it in columns field of matrix viz

2. Create a new column, simply assign it with your Measure and pull it in columns field of matrix viz:

New Percentile_Column = [Percentile_Meausure]

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!! 

[LP2803]

Hi Anand, Thank for the help.

I get incorrect result when i make my measure into a column

my measure is like "Percentilex.inc(table1,table1[salesamount])

Could you please help me how can i rewrite this in the column to get the same results.

[LP2803]

Please can any one help me with this. there is an urgent requirement for this.

Please help.

[Greg_Deckler]

@LP2803 - I believe you can have a hierarchy in your columns of your matrix. If all else fails, there is always this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...