Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @Anonymous
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
@Anonymous
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
Hi @Anonymous
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
@Anonymous
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |