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

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

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors