sumifs in Power BI

[LizziePeters]

Hi, I am trying to work out a way to sum up some values in a column if based on 2 or more criteria.

Using the below example, I would like to create a formula that sums the 'total rights' column IF the Year is 2019 and the Deal ID is 34.

Thank you so much for your help!

[amitchandak]

@LizziePeters , Try as measure or column

sumx(filter(Table, Table[year] =2019 && Table[deal Id] =34),Table[total rights])

[LizziePeters]

Thank you so much for the quick response!  How about if I want to do this for a much wider dataset?  Ie I don't want to mention "2019" and "34" in my formula?  I would just like the formula to look at the cells in that given column?  Thank you so much!

[v-yiruan-msft · Accepted solution]

Hi @LizziePeters ,

You can create two slicers base on field Year and Deal ID and create the related visual using the aggregate (Sum) of field Total Rights just as below screenshot.

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[amitchandak]

@LizziePeters ,

sumx(filter(Table, Table[year] =year(today())-1 && Table[deal Id] =34),Table[total rights])

for [deal Id]  you can use slicer

Or use time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))