DAX measure to sum a column in table A but can be filtered by variables in linked table B

[SariW]

I'm fairly beginner at DAX and am really struggling to figure this out.

I have a table (Table A below) that has a number of estimating stages with a total cost for each. I then need a column to calculate the latest cost estimate for that row, based on stage of estimating that the package ID as a whole is at. This is done with a mix of measures and a calculated column.

I first created measures for each of the total columns to give a total by Package ID, see below using Total C as an example:

Total C by PKG =

    CALCULATE(

        SUM('Table A'[Total C]),

        ALLEXCEPT('Table A','Table A'[Package ID])

    )

I then created a calculated column for Estimated Final Cost (EFC) using SWITCH to give the cost for each row from the latest estimating stage.

The calculated EFC column DAX formula is:

ESTIMATED FINAL COST =

    SWITCH(true(),

        [Total C by PKG] <> 0, 'Table A'[Total C],

        [Total B by PKG] <> 0, 'Table A'[Total B],

        'Table A'[Total A]

        )

This column seems to be working fine in the context of the table.

I now need to input this measure into a visual, showing EFC by Package, and another visual by Discipline. Discipline comes from table B, and the tables are linked by the Package ID column.

I have then created another measure based off this calculated column to sum the EFC column:

EFC = sum('Table A'[ESTIMATED FINAL COST])

When I input the EFC measure into a bar graph with Discipline (from Table B) on the x-axis, the visual only shows the total EFC against a (Blank) discipline (see image at the bottom of the post).

I need this EFC measure (and other measures that will be calculated in a similar way) to be able to be filtered by Discipline from Table B, or be broken out in a table with Package Name from Table B.

Any assistance would be appreciated!

TABLE A

PACKAGE ID	Description	TOTAL A	TOTAL B	TOTAL C	ESTIMATED FINAL COST
80233266	Piling prep & distribution of material	26,665	31,332		31,332
80233266	Install Pile	74,057	70,112		70,112
51480994	Supply capping	769,108			769,108
51480994	Stockpile capping	384,565			384,565
53683210	Install trackside equipment foundation	570,666	571,000	555,637	555,637

TABLE B

Discipline	Package ID	Package Name
ENABLING	80233266	TRACK ENABLING
BUILDING	34284038	OHW ENABLING
CIVIL	51480994	CSR STAGE 1
UTILITIES	87811765	RACK CROSSINGS - GAS
BUILDING	85985437	AREA 1 PERMANENT OHW
BUILDING	93623530	AREA 2 PERMANENT OHW
RAIL	53683210	ESDS
RAIL	86890401	SUBSTATION
RAIL	85201749	LOCAL SIGNAL CONNECTION

Bar graph with X-axis set to Discipline (from Table B), and Y-axis the EFC measure.

[Ashish_Mathur]

Hi,

Create a Dim Table of Package ID and build a relationship (Many to One and Single) from the Package ID of both Fact tables to this Dim Table.

[SariW]

Hi Ashish,

Thanks for the response. Table B in this case is a dimension table (I believe). The Package ID column in Table B only has distinct values, so the link between Table A and Table B, using Package ID, is a many to one relationship. Sorry if I haven't fully understood your response, I'm learning on the fly!

[Ashish_Mathur]

You are correct.  Please share the download link of the PBi file.

[SariW]

Hi Ashish,

I have just created a new dashboard based on the example tables and formulas I listed in my original post, and now everything is working. The example above was a heavily simplified version of my actual data set, so I'm guessing it's not the formulas that are the problem, but something to do with my actual data.

I'll have to dig further into my data and see if I can figure this out. Unfortunately I can't share the actual data set due to confidentiality, but any thoughts on areas I could look in would be appreciated.

Thanks,

Sari