Forum Discussion

Icon for Helper I rank

Helper I

9 years ago

Average of Measure in Direct Query mode

Currently I have a measure calculating the days between the start and end dates and getting a value in days: Life In Days = DateDiff(Max([StartDateNzt]), Max(LoanStatusHistories[CreateDateNzt]), DAY...

Anonymous

Not applicable

9 years ago

Since I'm not very clear for you table struct and relationships, can you share a part of your sample data to test?

Regards,

Xiaoxin Sheng

Icon for Helper I rank

Helper I

9 years ago

No problem Anonymous. What format would suit you best?

Anonymous
Not applicable
9 years ago
Hi cgardyne,

You can upload a pbix file with part of sample data and share us the link.

Regards,

Xiaoxin Sheng
- cgardyne
  Helper I
  9 years ago
  
  I've used Data Import mode to import some sample data from my local db into pbix - not sure how much you'll be able to see?
  
  Basically Loan has multiple LoanStatusHistories which has a one to one to LoanStatus. Here's the link to the pbix - https://drive.google.com/file/d/0Bx18tlhBvGkFdHNwbHphMWUwSE0/view
  
  As you can see on the left are all the LoanStatusHistories for each Loan and on the right are 3 tables where I'm filtering the Loans down by LoanStatus[Display] - Active, Pre Approved and Awaiting User Verification.
  
  You can see that summing the Amount in the tables is always $50,000 (total of all loans irrespective of filter).
  - Anonymous
    Not applicable
    9 years ago
    Hi cgardyne,
    
    I got the sample file, but I can't find the "StartDateNzt" column/measure, can you fix it?
    
    >>You can see that summing the Amount in the tables is always $50,000 (total of all loans irrespective of filter).
    
    Since your original table only has three records, when at the Total row, table visual will try to calculate summary result with column values which from whole original table.(the calculation may similar as: Calculate(sum(Loans[Amount]),ALL(Loans))
    
    Regards,
    
    Xiaoxin Sheng