Forum Discussion

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive Resident
4 years ago

Combine 2 data source (excel) and summarize column

HI I have the table below with different tasks. I created a table  showing CID & Client Name with Task= Greenlight Checks and Task Status Closed. Power BI table:     My problem...
Need Help
romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive Resident
4 years ago

Hi JirkaZ

 

I have 2 data sources(tables). One is called BMPS LOF Report and the other one is called SFF. I need the SFF to get the USD amount.

I am using the field "CID" that is the same in both files. 

 
What do you mean about "switch it around and loop"? DO I need to add your formula in the return part of my formula? thanks
 
 
USD total=
var tab =
CALCULATETABLE (
VALUES ( 'BMPS LOF Report'[CID] ),
FILTER (
'BMPS LOF Report',
'BMPS LOF Report'[TASK] = "GreenLight Checks"
&& 'BMPS LOF Report'[TASK_STATUS] = "Closed"

)

)

return

CALCULATE (

SUMX (tab,CALCULATE(SUM(SFF[USD], SFF[CID] = [cid])) ),

INTERSECT ( VALUES('BMPS LOF Report'[CID] ), tab )

)
JirkaZ's avatar
JirkaZ
Icon for Solution Specialist rankSolution Specialist
4 years ago

Yes, you would use my suggestion in the RETURN section of your formula. 

 

But if you checked your original formula closely, then the INTERSECT function is using only the BMPS LOF Report table on both sides so it cannot really work correctly.