Forum Discussion

jaco1951's avatar
jaco1951
Helper III
7 years ago

Another sub total issue

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

Show and Tell

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    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.