Combine 2 data source (excel) and summarize column

Question

HII have the table below with different tasks. I created a table &nbsp;showing CID &amp; Client Name with Task= Greenlight Checks and Task Status Closed.Power BI table:&nbsp;&nbsp;My problem is that when I combine with another data source (Excel) to get the amount of USD ($) x client, the table shows Total Column amount instead of showing only the CID total ColumnIn my case CID 11680 should give= $34681 instead of $759286291&nbsp;Any Hint?&nbsp;&nbsp;I am trying but with no exit...&nbsp;GreenLight Closed =var tab =&nbsp;&nbsp;&nbsp; CALCULATETABLE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES ( 'BMPS LOF Report'[CID] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'BMPS LOF Report',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'BMPS LOF Report'[TASK] = "GreenLight Checks"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; 'BMPS LOF Report'[TASK_STATUS] = "Closed"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )return&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMX ( 'SFF'[USD] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INTERSECT ( VALUES('BMPS LOF Report'[CID] ), tab )&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;Thanks

jirkaz · Answer

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 · Answer

Hi JirkaZ&nbsp;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.&nbsp;&nbsp;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&nbsp;&nbsp;USD total=var tab =CALCULATETABLE (VALUES ( 'BMPS LOF Report'[CID] ),FILTER ('BMPS LOF Report','BMPS LOF Report'[TASK] = "GreenLight Checks"&amp;&amp; 'BMPS LOF Report'[TASK_STATUS] = "Closed"))returnCALCULATE (SUMX (tab,CALCULATE(SUM(SFF[USD], SFF[CID] = [cid])) ),INTERSECT ( VALUES('BMPS LOF Report'[CID] ), tab ))

jirkaz · Answer

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.

romovaro · Answer

Hi JirkaZ&nbsp;I think I have problems wih the last "CID"...&nbsp;&nbsp;SUMX(tab, CALCULATE(SUM(SFF[usd]), SFF[CID] = [cid]))&nbsp;&nbsp;&nbsp;&nbsp;

stevehailey · Answer

Hi&nbsp;romovaro. I see in the screenshot of the data model that there is a many-to-many relationship between the tables. How is Power BI to know which USD rate you want it to grab when there are multiple rates? I'd suggest fixing the data model so that the exchange rates are on the "one-side" of a 1-to-many relationship.&nbsp;Then you can easily used the RELATED function to grab the correct rate.&nbsp; You may need to create a third table, a bridging table.&nbsp;This documentation from Microsoft should be helpful:&nbsp;Many-to-many relationship guidance - Power BI | Microsoft Docs

Forum Discussion

Combine 2 data source (excel) and summarize column

7 Replies

Related content

Tables combine

Parameter in summarize

Don't Summarize....still summarizes...?

DISTINCT UNION & Summarized Combined.

GroupBY and summarize