Combine 2 data source (excel) and summarize column

[romovaro]

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 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 Column

In my case CID 11680 should give= $34681 instead of $759286291

Any Hint? 

I am trying but with no exit...

GreenLight Closed =

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 ( 'SFF'[USD] ),

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

    )

Thanks

[SteveHailey]

Hi @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. Then you can easily used the RELATED function to grab the correct rate.  You may need to create a third table, a bridging table. 

This documentation from Microsoft should be helpful: Many-to-many relationship guidance - Power BI | Microsoft Docs

[romovaro]

HI Steve. Agreed. The only number that is 1 to 1 is the CUID. (Unique customer number) and now all the connections between tables are CUID to CUID. THe CID can contain different local CUID.

Currently I use the Excel formula SUMIF where I select the list of CID and USD dollars from the SFF File and use the CID list from a Pivot table in the BMPS file as criteria

=SUMIF('Data Source-SFF'!A:A;'PT-Greenlights'!A35;'Data Source-SFF'!BS:BS)

[romovaro]

HI Steve. Checked the article but still cannot make it. I created a "manual" table in Excel in order to get the total USD of the CID and then  connect the CID from the "Manual CID table" and the BPMS table...but again...it's an extra manual work I need to create instead of an automatially refreshed. Any tip would be welcome. thankls

[romovaro]

Hi JirkaZ

I think I have problems wih the last "CID"...

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

[JirkaZ]

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]

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]

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.