The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi all,
Need help with something i'm building... In Excel this would be really easy but struggling to find a solution in Power BI.
I have some data on products and the competitor equivalents. I've included a made up example of how the data would be:
Period | Product | Volume |
P1 | COCA-COLA - 330ML / X24 - CAN | 10,000 |
P2 | COCA-COLA - 330ML / X24 - CAN | 9,000 |
P3 | COCA-COLA - 330ML / X24 - CAN | 8,000 |
P4 | COCA-COLA - 330ML / X24 - CAN | 7,000 |
P5 | COCA-COLA - 330ML / X24 - CAN | 6,000 |
P6 | COCA-COLA - 330ML / X24 - CAN | 5,000 |
P1 | PEPSI - 330ML / X24 - CAN | 3,200 |
P2 | PEPSI - 330ML / X24 - CAN | 3,000 |
P3 | PEPSI - 330ML / X24 - CAN | 2,800 |
P4 | PEPSI - 330ML / X24 - CAN | 2,600 |
P5 | PEPSI - 330ML / X24 - CAN | 2,400 |
P6 | PEPSI - 330ML / X24 - CAN | 2,200 |
P1 | FANTA - 2000ML / X1 - PET | 5,000 |
P2 | FANTA - 2000ML / X1 - PET | 4,000 |
P3 | FANTA - 2000ML / X1 - PET | 3,000 |
P4 | FANTA - 2000ML / X1 - PET | 2,000 |
P5 | FANTA - 2000ML / X1 - PET | 1,000 |
P6 | FANTA - 2000ML / X1 - PET | 500 |
P1 | TANGO - 2000ML / X1 - PET | 4,600 |
P2 | TANGO - 2000ML / X1 - PET | 4,400 |
P3 | TANGO - 2000ML / X1 - PET | 4,200 |
P4 | TANGO - 2000ML / X1 - PET | 4,000 |
P5 | TANGO - 2000ML / X1 - PET | 3,800 |
P6 | TANGO - 2000ML / X1 - PET | 3,600 |
P1 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 2,000 |
P2 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,800 |
P3 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,600 |
P4 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,400 |
P5 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,200 |
P6 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,000 |
P1 | PEPSI MAX - 330ML / X1 - CAN | 1,400 |
P2 | PEPSI MAX - 330ML / X1 - CAN | 1,150 |
P3 | PEPSI MAX - 330ML / X1 - CAN | 900 |
P4 | PEPSI MAX - 330ML / X1 - CAN | 650 |
P5 | PEPSI MAX - 330ML / X1 - CAN | 400 |
P6 | PEPSI MAX - 330ML / X1 - CAN | 150 |
I then want to add a "Comparator Product" column - this is easy, as i have a lookup table which i can then use the Lookupvalue DAX to get this populated:
I then want "Comparator Product Volume", so the final table will look somthing like this:
Period | Product | Volume | Comparator Product | Comparator Product Volume |
P1 | COCA-COLA - 330ML / X24 - CAN | 10,000 | PEPSI - 330ML / X24 - CAN | 3,200 |
P2 | COCA-COLA - 330ML / X24 - CAN | 9,000 | PEPSI - 330ML / X24 - CAN | 3,000 |
P3 | COCA-COLA - 330ML / X24 - CAN | 8,000 | PEPSI - 330ML / X24 - CAN | 2,800 |
P4 | COCA-COLA - 330ML / X24 - CAN | 7,000 | PEPSI - 330ML / X24 - CAN | 2,600 |
P5 | COCA-COLA - 330ML / X24 - CAN | 6,000 | PEPSI - 330ML / X24 - CAN | 2,400 |
P6 | COCA-COLA - 330ML / X24 - CAN | 5,000 | PEPSI - 330ML / X24 - CAN | 2,200 |
P1 | PEPSI - 330ML / X24 - CAN | 3,200 | - | 0 |
P2 | PEPSI - 330ML / X24 - CAN | 3,000 | - | 0 |
P3 | PEPSI - 330ML / X24 - CAN | 2,800 | - | 0 |
P4 | PEPSI - 330ML / X24 - CAN | 2,600 | - | 0 |
P5 | PEPSI - 330ML / X24 - CAN | 2,400 | - | 0 |
P6 | PEPSI - 330ML / X24 - CAN | 2,200 | - | 0 |
P1 | FANTA - 2000ML / X1 - PET | 5,000 | TANGO - 2000ML / X1 - PET | 4,600 |
P2 | FANTA - 2000ML / X1 - PET | 4,000 | TANGO - 2000ML / X1 - PET | 4,400 |
P3 | FANTA - 2000ML / X1 - PET | 3,000 | TANGO - 2000ML / X1 - PET | 4,200 |
P4 | FANTA - 2000ML / X1 - PET | 2,000 | TANGO - 2000ML / X1 - PET | 4,000 |
P5 | FANTA - 2000ML / X1 - PET | 1,000 | TANGO - 2000ML / X1 - PET | 3,800 |
P6 | FANTA - 2000ML / X1 - PET | 500 | TANGO - 2000ML / X1 - PET | 3,600 |
P1 | TANGO - 2000ML / X1 - PET | 4,600 | - | 0 |
P2 | TANGO - 2000ML / X1 - PET | 4,400 | - | 0 |
P3 | TANGO - 2000ML / X1 - PET | 4,200 | - | 0 |
P4 | TANGO - 2000ML / X1 - PET | 4,000 | - | 0 |
P5 | TANGO - 2000ML / X1 - PET | 3,800 | - | 0 |
P6 | TANGO - 2000ML / X1 - PET | 3,600 | - | 0 |
P1 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 2,000 | PEPSI MAX - 330ML / X1 - CAN | 1,400 |
P2 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,800 | PEPSI MAX - 330ML / X1 - CAN | 1,150 |
P3 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,600 | PEPSI MAX - 330ML / X1 - CAN | 900 |
P4 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,400 | PEPSI MAX - 330ML / X1 - CAN | 650 |
P5 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,200 | PEPSI MAX - 330ML / X1 - CAN | 400 |
P6 | COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN | 1,000 | PEPSI MAX - 330ML / X1 - CAN | 150 |
P1 | PEPSI MAX - 330ML / X1 - CAN | 1,400 | - | 0 |
P2 | PEPSI MAX - 330ML / X1 - CAN | 1,150 | - | 0 |
P3 | PEPSI MAX - 330ML / X1 - CAN | 900 | - | 0 |
P4 | PEPSI MAX - 330ML / X1 - CAN | 650 | - | 0 |
P5 | PEPSI MAX - 330ML / X1 - CAN | 400 | - | 0 |
P6 | PEPSI MAX - 330ML / X1 - CAN | 150 | - | 0 |
For the Comparator Product Volume column, in excel this is easy to do, as you can write a sumifs like:
I want to know - is there a way to do this in Power BI to get the Comparator Product Volume? I'm struggling to figure out how to do it.
Thanks.
Solved! Go to Solution.
@alexpegg86 , you can achieve this in Power Query as well, but here are DAX options:
comp_value_measure =
VAR comp_product = SELECTEDVALUE ( Table__[Comparator Product] )
VAR period = SELECTEDVALUE ( Table__[Period] )
VAR res =
CALCULATE (
MAX ( Table__[Volume] ),
ALL ( Table__ ),
Table__[Period] = period,
Table__[Product] = comp_product
)
RETURN
res
comp_value_column =
VAR comp_product = Table__[Comparator Product]
VAR period = Table__[Period]
VAR res =
CALCULATE (
MAX ( Table__[Volume] ),
ALL ( Table__ ),
Table__[Period] = period,
Table__[Product] = comp_product
)
RETURN
res
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@alexpegg86 , do you need a measure or a calculated column? Is "Comparator Product" a measure or a calculated column?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @ERD ,
I think a column would be the best solution, but if there's a way to do it with a measure then open to that too! At least with a column you can see the result in the data table view.
@alexpegg86 , you can achieve this in Power Query as well, but here are DAX options:
comp_value_measure =
VAR comp_product = SELECTEDVALUE ( Table__[Comparator Product] )
VAR period = SELECTEDVALUE ( Table__[Period] )
VAR res =
CALCULATE (
MAX ( Table__[Volume] ),
ALL ( Table__ ),
Table__[Period] = period,
Table__[Product] = comp_product
)
RETURN
res
comp_value_column =
VAR comp_product = Table__[Comparator Product]
VAR period = Table__[Period]
VAR res =
CALCULATE (
MAX ( Table__[Volume] ),
ALL ( Table__ ),
Table__[Period] = period,
Table__[Product] = comp_product
)
RETURN
res
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Thanks @ERD! plugged that formula in and it worked 😀
The actual dataset i'm working with is much bigger, so im going to play around with the formula to see if i can get it to work on a larger dataset. I'll post again if i need some more help.
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |