Another sub total issue

[jaco1951]

Hi

I have trouble getting the correct subtotal. I have a series of loans (factTable), which is linked to a security table (dimSecurity) distinct number for each facility.

These facilities is then linked to a table (dimBank) where each facility has multiple lenders with various share of the loan.

So in a case I could have a loan at USD 100.000 where Citibank holds 40% of the loan, and Deutsche Bank could have the remaining 60%.

If I would like to see how much of my loans that are located at Citibank, I use the following DAX measure:

Loan by bank = CALCULATE(SUMX(factTable;factTable[Loan Balance]) * SUMX(dimBank;dimBank[Share%])) / COUNTROWS(DISTINCT(dimBank[%BankList]))

The problem is that I cannot get the subtotal to do the math as it looks like in the table. 

My  subtotal is calculated like this:

Sum of loan * Sum of share in % / Number of rows



Can anyone assist me on how to get the subtotal to be the same as the filter I use?

Br Espen

[Greg_Deckler]

So typically in cases like this I use the following method. First, keep your measure the way it is and then write this measure and use this measure in your visualization:

My Measure in DAX 1 = 
VAR __table = SUMMARIZE('Table',[ClassicLoanName],[Loan balance],[Share%],[Count rows],"__Value",[My Measure in DAX])
RETURN
IF(HASONEVALUE([ClassicLoanName]),[My Measure in DAX],SUMX(__table,[__Value]))

Basically, you recreate the visualization in memory using SUMMARIZE and your original measure and then you just sum up the column, which is what you are expecting to happen in the table visualization but doesn't.

[jaco1951]

Thanks Greg.

I'm not sure how to make it work as the [Loan balance] data is coming from the factTable, Can I mix tables in the variable?

[v-danhe-msft]

Hi @jaco1951,

Could you please share your pbix file if possible? You could upload your file to your Onedrive or Dropbox and send the link to me,

Regards,

Daniel He

[jaco1951]

I made a workaround. It is tricky to recrate the dataset as it is very large in both amount of tables and rows of data. And all the data is sensitive.... Otherwise I would be happy to share it.

Thanks anyway.

Br Espen

[Greg_Deckler]

Well, details tend to matter. Should be solvable but would need exact information on your scenario. 

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