sum with filters...

[Birch]

Hi,

I have 2 tables - one wih salespersons names and there budget for the year (and pr. month) and one table with all sales for all salespersons.

I need to do a SUM of units pr. salesperson and pr. timeframe (MTD and YTD)

In Excel I can do a SUMIF and use the salespersons name as a VAR, but how can I do this in PowerBI Desktop?

Thanks

[MattAllington]

Welcome to power bi - where to start?!  You don't really give enough info to give a clear answer. The short answer is you use the formula =CALCULATE (sum(tableName[units column]),tablename[sales person]="Birch") but believe me the long answer is much more involved. You can learn it, and you will learn quickly if you read a book like the one I wrote specifically for Excel users leaning Power Pivot/Power BI. http://xbi.com.au/learndax

[Birch]

Hi

I used this

SalesYTD = CALCULATE(SUM(OpportunitySet[No. of Units]);OpportunitySet[This Year Closed]="YES";OpportunityStateOptionSet[Status]="WON")

this works for me 🙂

[Birch]

This leads me into me next problem - I have 12 columns (named 1, 2, 3.... 12) and I need a new column showing the number in the column with name = MONTH(TODAY()) - can I do that?

[v-caliao-msft · Accepted solution]

Hi Birch,

According to your description, it seems that you need a dynamic column name, right?

Based on my research, we cannot use DAX expression to define a column name which you can see on the screenshot below.

If this is not what you want, please elaborate your requirement, so that we can make further analysis.

Regards,
Charlie Liao