Total row doesn't match with the individual row sum in Many to Many relationship using Bridge table

[Anonymous]

I have implemented many to many using Bridge table. (TableA *<--1 Bridge 1<->* TableB).

Now i have dropped an attribute from TableB and

DISTINCTCOUNT(TableA[Column1])

into a table visual. I was not able to match the individual row sum with Total row at the bottom of the visual. I know why i was not able to match because each row in TableA might be get shared across two rows in TableB (which is a double count of the same row), when it comes to Total row it is counted as one since we are using DistinctCount. I could use

SUMX(VALUES(TableB[Column1]),DISTINCTCOUNT(TableA[Column1]))

but if we have 5 columns in TableB i should write this SUMX 5 times by replacing the column value in the VALUES function and also it's not going to work because while calculating the Total row we can not get to know which column is being used in the visual so that that particular column should be used in SUMX(VALUES())). I tried using this

CALCULATE(DISTINCTCOUNT(TABLEA[Column1]_,Bridge)

 It doesn't work either.

Is there any way that i could overcome this issue ? Thanks in advance.

[Greg_Deckler]

Would need some sample data that recreates the issue. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Also, this looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

Thanks for the reply. I have attached the sample data. Please take a look. In Sales Program table i have only one attribute incase of sample but in my real data I have almost 10 attributes which are having the same issue. Please download the sample pbix file from this link Sample Data

[Anonymous]

@Greg_Deckler Can you please take a look at this ? Thanks! I have attached the sample pbix file.

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may add CALCULATE as follows.

Measure =
SUMX (
    VALUES ( 'Sales Program'[Sales Industry] ),
    CALCULATE ( DISTINCTCOUNT ( Pipeline[Dim_OpportunityId] ) )
)