Forum Discussion

cgardyne's avatar
cgardyne
Icon for Helper I rankHelper 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...
Need Help
Anonymous's avatar
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's avatar
cgardyne
Icon for Helper I rankHelper 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's avatar
    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

    • cgardyne's avatar
      cgardyne
      Icon for Helper I rankHelper I
      9 years ago

      Hi Anonymous,

       

      I've added StartDateNzt and added the measure for Loan Life in Days as you'll see in the tables on the right.

       

      Thanks,

      Col

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi cgardyne,

         

        >>When I add the AverageX measure below it doesn't filter down. Basically no matter what filter I apply it always gives me the same result?

        You can modify the cross filter direction option to both to fix this issue.

         

         

        Regards,

        Xiaoxin Sheng