count value from multiple columns and multiple rows

[wendylinlin]

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 · Accepted solution]

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

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

[az38 · Accepted solution]

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

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

[az38 · Accepted solution]

@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

[wendylinlin]

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.