Sumif Funtion in Power BI Desktop

[Chocnut]

Hi,

I have a data table as below, there are "Vendor", "Team" and respective "Spend".

After I load this table into Power BI Desktop, I would like to see the Vendors which have both Spend in Team A and B in a new table, i was trying to use Calculate or Sumx but can not figure out a way to do it, can someone help me with this? Thanks a lot.

Original Data:

Vendor	Team	Spend
A	X	2000
B	X	3000
C	Y	1000
A	Y	2000
C	Z	2000
B	Y	5000
B	X	2000

Data required:

Vendor	Team A Spend	Team B Spend
A
B

[v-shex-msft]

HI @Chocnut,

You can use matrix visual to achieve your requirement.

Drag 'Vendor' to rows, 'Team' to column, 'Spend' to values field.

Regards,

Xiaoxin Sheng

[Chocnut]

Thanks a lot @v-shex-msft, much appreciate your solution.

Is it possible to use DAX instead of using Matrix to create a table? Because I only want to see those vendors who incurred expenses for both Team X and Y, I do not want to see Team Z here.

[v-shex-msft]

Hi @Chocnut,

>>Because I only want to see those vendors who incurred expenses for both Team X and Y, I do not want to see Team Z here.

You can add a filter/slicer to filter on team to achieve this simply.

Use dax formula to create a summary table also suitable this, but I think formula will more complex than above.

Regards,

Xiaoxin Sheng

[Chocnut]

Hi @v-shex-msft, thanks for the prompt response.

Although we can apply filter to remove Team Z here, however how to remove vendors do not have spend for both Team X and Y.

For Example, Team C should not appear in the Matrix here...

I'm not saying Matrix is not good, it's just not a perfect solution to my request...

I tried to use Summarize function however can not figure out how, do you have any idea? Thanks.

[v-shex-msft]

Hi @Chocnut,

>>For Example, Team C should not appear in the Matrix here...

Actually, it will disappear when you fully filter item exist in specific vendor.

For e.g. I only keep team x in filter, vendor c will disappear.

>>I tried to use Summarize function however can not figure out how, do you have any idea?

Normal summarize can't generate the reuslt, you need to write some other steps to generate detail team columns.

BTW,you can also try to use pivot column feature(query editor) to generate the detail table.

Sample:

Regards,

Xiaoxin Sheng

[Chocnut]

Hi @v-shex-msft,

Much appreicate your help here, I just got a new skill from you 🙂

Maybe I didn't explain properly in my previous post, I need to only have Vendors who incurred BOTH spend with Team X and Y, for example, Vendor C doesn't incur spend with Team X, so should not include in the table.

The correct result should be shown as below:

Vendor	Team X Spend	Team Y Spend
A	2000	2000
B	5000	5000

Thanks mate.

[v-shex-msft]

Hi @Chocnut,

>>Maybe I didn't explain properly in my previous post, I need to only have Vendors who incurred BOTH spend with Team X and Y, for example, Vendor C doesn't incur spend with Team X, so should not include in the table.

Normal slicer/filter can't achieve your requirement.
In my opinion, I'd like to suggest you write a measure as tag to filter on records.

Sample:

1. Create selector table.

Selector = VALUES(Sample[Team])

2. Write measure to compare current list and selected list.

Flag = 
var current_vendor=LASTNONBLANK('Sample'[Vendor],[Vendor])
var current_team=CALCULATETABLE(VALUES('Sample'[Team]),'Sample'[Vendor]=current_vendor)
var seleted_team=ALLSELECTED(Selector[Team])
var conditions=IF(COUNTROWS(seleted_team)<2,seleted_team in current_team,CONCATENATEX(INTERSECT(current_team,seleted_team),[Team],",")=CONCATENATEX(current_team,[Team],","))
return
IF(conditions,"Y","N")

3. Create matrix visual and drag flag measure to visual level filter, then switch filter mode to 'is', result Y. 

4. Create slicer based on selector table.

Result:

Regards,

Xiaoxin Sheng

[Chocnut]

Hi @v-shex-msft,

Thanks for the solution provided.

I tried to write the measures however it seems i can not locate "VAR" as the DAX syntax, what shows there are "VAR.P", "VAR.S", "VARX.P" and "VARX.S", I'm not very familiar with VAR, is this something normal? Thanks

[v-shex-msft]

HI @Chocnut,

Var function is used to define the variable, you not need to care about the auto-spell notice:

VAR (DAX)

Regards,

Xiaoxin Shengt

[Chocnut · Accepted solution]

Thanks @v-shex-msft.

I finally figured out an easier way to do it by using Summerize to generate the list of Vendors, then use Calculate to capture spend for other teams, and use ISBLANK to filter out the vendors have shared spend between 2 teams.

[Chocnut]

 Hi,

I have a table as below and I would like to see the vendors which have both spend under Team X and Team Y in a new table (not via table or matrix), i tried to use Summarize but can not figure out how to do that, can someone help with it? Thanks a lot.

Original Data:

Vendor	Team	Spend
A	X	2000
B	X	3000
C	Y	1000
A	Y	2000
C	Z	2000
B	Y	5000
B	X	2000

Data required:

Vendor	Team X Spend	Team Y Spend
A
B

[Chocnut]

Hello guys, can someone pls assist me?

[Chocnut]

Hi there,

Could someone help me with this please? Thanks.