Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jaco1951
Helper III
Helper III

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

Capture.JPG

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

Br Espen

5 REPLIES 5
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.