Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
alexpegg86
Helper I
Helper I

DAX formula similar to a sumifs in Excel

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:

 

PeriodProductVolume
P1COCA-COLA - 330ML / X24 - CAN10,000
P2COCA-COLA - 330ML / X24 - CAN9,000
P3COCA-COLA - 330ML / X24 - CAN8,000
P4COCA-COLA - 330ML / X24 - CAN7,000
P5COCA-COLA - 330ML / X24 - CAN6,000
P6COCA-COLA - 330ML / X24 - CAN5,000
P1PEPSI - 330ML / X24 - CAN3,200
P2PEPSI - 330ML / X24 - CAN3,000
P3PEPSI - 330ML / X24 - CAN2,800
P4PEPSI - 330ML / X24 - CAN2,600
P5PEPSI - 330ML / X24 - CAN2,400
P6PEPSI - 330ML / X24 - CAN2,200
P1FANTA - 2000ML / X1 - PET5,000
P2FANTA - 2000ML / X1 - PET4,000
P3FANTA - 2000ML / X1 - PET3,000
P4FANTA - 2000ML / X1 - PET2,000
P5FANTA - 2000ML / X1 - PET1,000
P6FANTA - 2000ML / X1 - PET500
P1TANGO - 2000ML / X1 - PET4,600
P2TANGO - 2000ML / X1 - PET4,400
P3TANGO - 2000ML / X1 - PET4,200
P4TANGO - 2000ML / X1 - PET4,000
P5TANGO - 2000ML / X1 - PET3,800
P6TANGO - 2000ML / X1 - PET3,600
P1COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN2,000
P2COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,800
P3COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,600
P4COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,400
P5COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,200
P6COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,000
P1PEPSI MAX - 330ML / X1 - CAN1,400
P2PEPSI MAX - 330ML / X1 - CAN1,150
P3PEPSI MAX - 330ML / X1 - CAN900
P4PEPSI MAX - 330ML / X1 - CAN650
P5PEPSI MAX - 330ML / X1 - CAN400
P6PEPSI MAX - 330ML / X1 - CAN150

 

 

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:

 

PeriodProductVolumeComparator ProductComparator Product Volume
P1COCA-COLA - 330ML / X24 - CAN10,000PEPSI - 330ML / X24 - CAN3,200
P2COCA-COLA - 330ML / X24 - CAN9,000PEPSI - 330ML / X24 - CAN3,000
P3COCA-COLA - 330ML / X24 - CAN8,000PEPSI - 330ML / X24 - CAN2,800
P4COCA-COLA - 330ML / X24 - CAN7,000PEPSI - 330ML / X24 - CAN2,600
P5COCA-COLA - 330ML / X24 - CAN6,000PEPSI - 330ML / X24 - CAN2,400
P6COCA-COLA - 330ML / X24 - CAN5,000PEPSI - 330ML / X24 - CAN2,200
P1PEPSI - 330ML / X24 - CAN3,200-0
P2PEPSI - 330ML / X24 - CAN3,000-0
P3PEPSI - 330ML / X24 - CAN2,800-0
P4PEPSI - 330ML / X24 - CAN2,600-0
P5PEPSI - 330ML / X24 - CAN2,400-0
P6PEPSI - 330ML / X24 - CAN2,200-0
P1FANTA - 2000ML / X1 - PET5,000TANGO - 2000ML / X1 - PET4,600
P2FANTA - 2000ML / X1 - PET4,000TANGO - 2000ML / X1 - PET4,400
P3FANTA - 2000ML / X1 - PET3,000TANGO - 2000ML / X1 - PET4,200
P4FANTA - 2000ML / X1 - PET2,000TANGO - 2000ML / X1 - PET4,000
P5FANTA - 2000ML / X1 - PET1,000TANGO - 2000ML / X1 - PET3,800
P6FANTA - 2000ML / X1 - PET500TANGO - 2000ML / X1 - PET3,600
P1TANGO - 2000ML / X1 - PET4,600-0
P2TANGO - 2000ML / X1 - PET4,400-0
P3TANGO - 2000ML / X1 - PET4,200-0
P4TANGO - 2000ML / X1 - PET4,000-0
P5TANGO - 2000ML / X1 - PET3,800-0
P6TANGO - 2000ML / X1 - PET3,600-0
P1COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN2,000PEPSI MAX - 330ML / X1 - CAN1,400
P2COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,800PEPSI MAX - 330ML / X1 - CAN1,150
P3COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,600PEPSI MAX - 330ML / X1 - CAN900
P4COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,400PEPSI MAX - 330ML / X1 - CAN650
P5COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,200PEPSI MAX - 330ML / X1 - CAN400
P6COCA-COLA ZERO / NO SUGAR - 330ML / X1 - CAN1,000PEPSI MAX - 330ML / X1 - CAN150
P1PEPSI MAX - 330ML / X1 - CAN1,400-0
P2PEPSI MAX - 330ML / X1 - CAN1,150-0
P3PEPSI MAX - 330ML / X1 - CAN900-0
P4PEPSI MAX - 330ML / X1 - CAN650-0
P5PEPSI MAX - 330ML / X1 - CAN400-0
P6PEPSI MAX - 330ML / X1 - CAN150-0

 

For the Comparator Product Volume column, in excel this is easy to do, as you can write a sumifs like: 

alexpegg86_0-1692873879058.png

 

 

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.

 

1 ACCEPTED SOLUTION
ERD
Community Champion
Community Champion

@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

ERD_0-1692890765967.pngERD_1-1692890780322.png

 

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!

View solution in original post

4 REPLIES 4
ERD
Community Champion
Community Champion

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

 

 

ERD
Community Champion
Community Champion

@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

ERD_0-1692890765967.pngERD_1-1692890780322.png

 

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.