cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wendylinlin
Frequent Visitor

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.

2 ACCEPTED SOLUTIONS
az38
Super User
Super User

Hi @wendylinlin 

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

Безымянный.png

 

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


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

View solution in original post

@wendylinlin 

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


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

View solution in original post

3 REPLIES 3
az38
Super User
Super User

Hi @wendylinlin 

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

Безымянный.png

 

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


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

@wendylinlin 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
wendylinlin
Frequent Visitor

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors