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
JirkaZ's avatar
JirkaZ
Icon for Solution Specialist rankSolution Specialist
4 years ago

Do I understand it correctly that you are basically intersecting the same table (BMPS LOF Report)? I think there should be the SFF table somewhere in the intersect statement. 

 

You could also switch it around and loop through the "tab" table like

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

  • 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.