Displaying measure resulted in text format as matrix row

[wlljhn]

Hi

I'm trying to figure out how to display measures as row on a matrix in powerbi like how I normally do by pivot on excel.

Here's the illustration:

ITEM	STOCK	STOCK CLUSTER
A	0	Out of Stock
B	50	More than 50 pcs
C	5	Less than 10 pcs
D	20	Less than 50 pcs
E	100	More than 50 pcs

First 2 columns are the data, while the STOCK CLUSTER column is a measure I created.

I then want to display them like this

CLUSTER	Count of ITEM
Less than 10 pcs	1
Less than 50 pcs	1
More than 50 pcs	2
Out of Stock	1
Grand Total	5

thank you

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[v-yangliu-msft]

Hi  @wlljhn ,

Its not possible to simply drag Measures to the Rows!

But you can do this:

Here are the steps you can follow：

1. Then Create a Helper Table

2. Select [CLUSTER] column, Column tools-Sort by column-ID

3. Create measure.

Count of ITEM = MAX('Table (2)'[ID])

4. Place the [CLUSTER] column in the Rows of the matrix, and place [Count of ITEM] in the Values of the matrix.

5. Result:

Best Regards,

Liu Yang

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

[amitchandak · Accepted solution]

@wlljhn , first you have to create a measure

stk = sum(Table[STOCK]) +0

then you have to create a table(say bucket table) with range and meaning cluster, start and end 

create a measure like this and use with bucket tbale

Countx(filter(values(Table[Item]), [Stk] >= min(bucket[start]) && [stk] <= max(bucket[end])), [item])

Refer to my video for more details

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k