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: 

Next up in the FabCon + SQLCon recap series: The roadmap for Microsoft SQL and Maximizing Developer experiences in Fabric. All sessions are available on-demand after the live show. 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
645 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.

Stand with Ukraine!

Here is an official way you can support Ukraine financially:
Come Back Alive foundation: https://savelife.in.ua/en/
Thank you!

View solution in original post

Message 2 of 4
609 Views
3 REPLIES 3
tamerj1
Community Champion
Community Champion

‎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
608 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.

Stand with Ukraine!

Here is an official way you can support Ukraine financially:
Come Back Alive foundation: https://savelife.in.ua/en/
Thank you!

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

‎11-28-2022 05:37 AM

thank you!

Message 3 of 4
577 Views
0

Helpful resources

Announcements
New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

FabCon and SQLCon Highlights Carousel

FabCon &SQLCon Highlights

Experience the highlights from FabCon & SQLCon, available live and on-demand starting April 14th.

March Power BI Update Carousel

Power BI Community Update - March 2026

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

View All