Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
anna-lee
Helper I
Helper I

Sum value relative to two metrics

‎11-23-2022 08:14 AM

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
Message 1 of 4
358 Views
0
1 ACCEPTED SOLUTION
ERD
Community Champion
Community Champion

‎11-23-2022 08:43 AM

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

Message 2 of 4
322 Views
3 REPLIES 3
tamerj1
Super User
Super User

‎11-23-2022 08:46 AM

Hi @anna-lee 
Please try

Result =
CALCULATE (
    SUM ( 'Table'[Value] ),
    ALLEXCEPT ( 'Table', 'Table'[Vendor Code], 'Table'[Year] )
)
Message 4 of 4
321 Views
0
ERD
Community Champion
Community Champion

‎11-23-2022 08:43 AM

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!

Message 2 of 4
323 Views
anna-lee
Helper I
Helper I
In response to ERD

‎11-28-2022 05:37 AM

thank you!

Message 3 of 4
290 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All