Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

count value from multiple columns and multiple rows

Hi all,

 

I have a list of bids against competitor informaiton, in each row, there's 3 columns to capture total 3 competitors. I need to calculate the total number of appearance accorss all 3 competitor columns for bid related to a specific product, and sum regardless of the column name(Competitor1,2,or 3)

 

For example, I have 7 bids here, for product A, B, and C:

For Bids of product A, Competitor AA,A1,A2,A3,A4 appears in 3 bids and I need to calculate the number of appearance of them.

 

 

BID number

Product

Competitor1

Competitor2

Competitor3

1

A

AA

A1

A2

2

B

B1

BB

B2

3

A

A4

AA

A3

4

C

CC

C1

C2

5

A

A1

A3

AA

6

C

C3

CC

C1

7

B

B3

B1

BB

 

My Expected calculation result is:

Product

Competitor Name

Number of times appeared

A

AA

3

A

A1

2

A

A2

1

A

A3

2

A

A4

1

B

BB

2

B

B1

2

B

B2

1

B

B3

1

C

CC

2

C

C1

2

C

C2

1

C

C3

1

 

I'm not sure what DAX to use to achieve this effect.

Please help.

 

Thank you.

  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    the best way is to use Power Query Mode:

    Select by left click + ctrl your columns Competitor1, Competitor2, Competitor2 then right click and Unpivot Only Selected columns.

     

    After, you will have 2 options:

    1. Apply data and simply aggregate in visual: Value (new field from previous step) and its count

    2. In power query mode right click on Value (new field from previous step)  -> Group By then

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

  • az38's avatar
    az38
    6 years ago

    Anonymous 

    you could create a calculated table

    Table = CROSSJOIN(DISTINCT('BIDS Table'[Product]);
UNION(
    SELECTCOLUMNS('BIDS Table';"Competitor";[Competitor1]);
    SELECTCOLUMNS('BIDS Table';"Competitor";[Competitor2]);
    SELECTCOLUMNS('BIDS Table';"Competitor";[Competitor3])
)
)

    then summarize in visual or create a new table

    Table Summ = SUMMARIZE('Table';'Table'[Product];'Table'[Competitor];"Number";countrows('Table'))

    do not hesitate to give a kudo to useful posts and mark solutions as solution

3 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    the best way is to use Power Query Mode:

    Select by left click + ctrl your columns Competitor1, Competitor2, Competitor2 then right click and Unpivot Only Selected columns.

     

    After, you will have 2 options:

    1. Apply data and simply aggregate in visual: Value (new field from previous step) and its count

    2. In power query mode right click on Value (new field from previous step)  -> Group By then

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • az38's avatar
      az38
      Community Champion
      6 years ago

      Anonymous 

      you could create a calculated table

      Table = CROSSJOIN(DISTINCT('BIDS Table'[Product]);
UNION(
    SELECTCOLUMNS('BIDS Table';"Competitor";[Competitor1]);
    SELECTCOLUMNS('BIDS Table';"Competitor";[Competitor2]);
    SELECTCOLUMNS('BIDS Table';"Competitor";[Competitor3])
)
)

      then summarize in visual or create a new table

      Table Summ = SUMMARIZE('Table';'Table'[Product];'Table'[Competitor];"Number";countrows('Table'))

      do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      After Unpivot the infomration seems grouped, but I still need all the 3 columns to be around, I need them for display purpose.

       

      there's remarks against each competitor, as competitor1, remarks1, competitor 2,remarks 2 etc.

      I need these columns to display remarks properly later.

       

      What should I do then? 

       

      Thank you.