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 t...
Show and Tell
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.

jaco1951's avatar
jaco1951
Helper III
7 years ago

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's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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's avatar
      jaco1951
      Helper III
      7 years ago

      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