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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
anna-lee
Helper I
Helper I

Sum value relative to two metrics

Hi,

 

I'm looking to sum the value of a table but still have it relative to the year and vendor code. Currently I can only get it relative to one column, but finding it difficult to do two. The result would be the column in blue below.

Vendor CodeDescriptionConcatYearValueResult
MA33C4accountsreceivableMA33C4accountsreceivable2021177414194
MA33C4accountsreceivableMA33C4accountsreceivable2020150113904
MA33C4accountsreceivableMA33C4accountsreceivable2019110310484
MA33C4accountsreceivableMA33C4accountsreceivable2018461520214
MA33C4accountsreceivableMA33C4accountsreceivable2017685220017
MA33C4accountsreceivableMA33C4accountsreceivable2016182710004
MA33C4accountspayableMA33C4accountspayable2021109414194
MA33C4accountspayableMA33C4accountspayable2020169713904
MA33C4accountspayableMA33C4accountspayable2019214410484
MA33C4accountspayableMA33C4accountspayable2018349820214
MA33C4accountspayableMA33C4accountspayable2017362420017
MA33C4accountspayableMA33C4accountspayable2016110910004
MA33C4revenueMA33C4revenue2021148014194
MA33C4revenueMA33C4revenue2020150213904
MA33C4revenueMA33C4revenue2019100210484
MA33C4revenueMA33C4revenue2018762420214
MA33C4revenueMA33C4revenue2017548920017
MA33C4revenueMA33C4revenue2016485010004
MA33C4costofgoodsMA33C4costofgoods2021984614194
MA33C4costofgoodsMA33C4costofgoods2020920413904
MA33C4costofgoodsMA33C4costofgoods2019623510484
MA33C4costofgoodsMA33C4costofgoods2018447720214
MA33C4costofgoodsMA33C4costofgoods2017405220017
MA33C4costofgoodsMA33C4costofgoods2016221810004
RQ020GaccountsreceivableRQ020Gaccountsreceivable202100
RQ020GaccountsreceivableRQ020Gaccountsreceivable2020100911802
RQ020GaccountsreceivableRQ020Gaccountsreceivable2019190312597
RQ020GaccountsreceivableRQ020Gaccountsreceivable2018156611546
RQ020GaccountsreceivableRQ020Gaccountsreceivable2017108313891
RQ020GaccountsreceivableRQ020Gaccountsreceivable201600
RQ020GaccountspayableRQ020Gaccountspayable202100
RQ020GaccountspayableRQ020Gaccountspayable2020430711802
RQ020GaccountspayableRQ020Gaccountspayable2019274312597
RQ020GaccountspayableRQ020Gaccountspayable2018227211546
RQ020GaccountspayableRQ020Gaccountspayable2017239713891
RQ020GaccountspayableRQ020Gaccountspayable201600
RQ020GrevenueRQ020Grevenue202100
RQ020GrevenueRQ020Grevenue2020358211802
RQ020GrevenueRQ020Grevenue2019423912597
RQ020GrevenueRQ020Grevenue2018400011546
RQ020GrevenueRQ020Grevenue2017532513891
RQ020GrevenueRQ020Grevenue201600
RQ020GcostofgoodsRQ020Gcostofgoods202100
RQ020GcostofgoodsRQ020Gcostofgoods2020290411802
RQ020GcostofgoodsRQ020Gcostofgoods2019371212597
RQ020GcostofgoodsRQ020Gcostofgoods2018370811546
RQ020GcostofgoodsRQ020Gcostofgoods2017508613891
RQ020GcostofgoodsRQ020Gcostofgoods201600
1 ACCEPTED SOLUTION
ERD
Community Champion
Community Champion

Hey @anna-lee ,

You can use the code below:

SumValue =
CALCULATE (
    SUM ( Table[Value] ),
    ALLEXCEPT ( Table, Table[Vendor Code], Table[Year] )
)

 

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

3 REPLIES 3
tamerj1
Super User
Super User

Hi @anna-lee 
Please try

Result =
CALCULATE (
    SUM ( 'Table'[Value] ),
    ALLEXCEPT ( 'Table', 'Table'[Vendor Code], 'Table'[Year] )
)
ERD
Community Champion
Community Champion

Hey @anna-lee ,

You can use the code below:

SumValue =
CALCULATE (
    SUM ( Table[Value] ),
    ALLEXCEPT ( Table, Table[Vendor Code], Table[Year] )
)

 

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!

thank you!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors