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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors
Top Kudoed Authors