Pivot on dynamic dataset

[Anonymous]

I have a dataset as below 

ColumnA	ColumnB
X	O1
Y	O1
Z	Z2
A	A3
B	A3
C	A3

It is highly possible, that the value changes as below 

ColumnA	ColumnB
X	O1
Y	O1
Z	O1
A	V1
B	V1
C	V1

The value for Z has been updated as O1, instead of Z2. 

My requirement is to @PivotTable this table using Column B 

Column A	O1	V1	Total
A		1	1
B		1	1
C		1	1
X	1		1
Y	1		1
Z	1		1

The values for column B against column A is dynamic. The pivot breaks the refresh of the datamodel. How this can be resolved.  

[Anonymous]

Hi @amitchandak , 

I need to pivot the table, as i need to the data model structure as below, which would be used in further calculations and computations.

Row Labels	O1	V1	Grand Total
A		1	1
B		1	1
C		1	1
X	1		1
Y	1		1
Z	1		1

There are several other columns like O1, V1. I have mentioned only 2 columns to illustrate the problem statement.

Note: I am not using the data table in the visualization directly. 

Thanks, 

Sangram

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your sample, if you select ColumnB and click Pivot Column like below:

You will get this table, not your expected outcome.

You can write a measure.

Count = COUNT('Table'[ColumnA])

Use a matrix like below, put ColumnA in Rows, ColumnB in Columns and the measure Count in Values. Get the expected output.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@Anonymous , In martix, You can have ColumnA on row and ColumnB on Column and count of ColumnB as values

or

Unpivot

https://radacad.com/pivot-and-unpivot-with-power-bi