cancel
Showing results for
Did you mean:
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.

Thank you.

2 ACCEPTED SOLUTIONS
Super User

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

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

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
3 REPLIES 3
Super User

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

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

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
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.

Announcements

#### The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

#### 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
Top Kudoed Authors