Pivot a pivot

[paxdolphin]

Hi,

Can someone help me with this?

I have Table A as shown below and i would like to count the number of ProposalID each person has (Table B). Thereafter, i would like to have another table or chart to show how many people have 1 proposal, 2 proposals, 3 proposals etc.

Thank you.

Table A

Name	ProposalID
Paul	333
Tom	222
Paul	431
Paul	321
James	521

Table B

Name	Count
Paul	3
Tom	1
James	1

[Anonymous · Accepted solution]

Hi @paxdolphin ,

1.In desktop, create a measure like

Count = CALCULATE(COUNT('Table'[ProposalID]),FILTER(ALLSELECTED('Table'),[Name]=MAX('Table'[Name])))

2.In Power Query, select the 'Name' and right click 'Group by'. 

Select 'Count Rows' and click OK.

Best Regards,

Stephen Tao

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

[paxdolphin]

sorry i think i wasn't clear. The end table which i would like to have is something like this. Can someone tell me how to achieve this? thank you 

X	Number of people who submitted x proposals
1	2
3	1

[Anonymous]

Hi @paxdolphin ,

I introduced two methods from previous reply.

You may download my attachment for details.

For example, the measure created in power bi desktop, which is the following.

Count = CALCULATE(COUNT('Table'[ProposalID]),FILTER(ALLSELECTED('Table'),[Name]=MAX('Table'[Name])))

 It's the results about the number of ProposalID group by name.

The results are displayed as a table visual in desktop.

Hope you understand.

Best Regards,

Stephen Tao

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

[drdataguy]

You can simply select the name and proposal ID fields and click bar graph to get the expected output: