Forum Discussion

paxdolphin's avatar
paxdolphin
New Member
3 years ago
Solved

Pivot a pivot

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

NameProposalID
Paul333
Tom222
Paul431
Paul321
James521

 

Table B

NameCount
Paul3
Tom1
James1
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

4 Replies

  • drdataguy's avatar
    drdataguy
    Frequent Visitor
    3 years ago

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      paxdolphin
      New Member
      3 years ago

      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 

       

      XNumber of people who submitted x proposals
      12
      31
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.