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: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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
286 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
250 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
249 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
251 Views
anna-lee
Helper I
Helper I
In response to ERD

‎11-28-2022 05:37 AM

thank you!

Message 3 of 4
218 Views
0

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

View All
Top Kudoed Authors
View All