Help needed! Can't get Card Visuals to Display Correct Totals of Matrix Table Column Measures

[julesdude]

Hi,

Help much needed here trying to get my DAX to give me the correct totals in my card visuals which reflect column totals in my matrix table. 

Currently I have 4 card visuals which give incorrect totals from 4 column measures in my matrix table. I want to sum:

- Total Valuation card visual - from the Last Valuation (Fund Currency) column of the table - this is a measure that works out the last valuation of a building based on the as of date selected by the user in the date picker in the report.

- Total Tenants card visual - this needs to count the total unique tenants per each Building Name. I'm close with my DAX measure which applied all the filtering to do this, but it is currently returning an error.

- Total Annual Net Rent card visual- is not correct total. It should sum everything from the Annual Net Rent column in the Lease_Unit table in the model once all leases are filtered. The DAX measure creating the column in the matrix table is correct, I just can't seem to get the total of this column correctly appearing in the vard visual.

- Total Occupancy card visual - should correctly reflect a percentage of all the percentages in the Occupancy column measure of the matrix table. The column is correct, but the card should show a percentage of the total occupancy. Again, for the life of me I can't get this to work.

Some monetary values are converted from local currency to fund currency where shown to be different, using the FX rate on a specified date or the as of date selected in the report date picker.

I've tried a lot of comibinations of DAX for all of these cards - from SUMX wrapping the measure inside of it, to trying to write out the entire measure again just for the card. But it just won't work and my knowledge is limited anyway.

I'd be hugely grateful if anyone can lend a hand. I've attached the pbix here as probably the best way to share.

[lbendlin]

Your formulas could use some more refactoring .  Remember that CALCULATE and CALCULATETABLE do the AND automatically.

see attached for a "fix" of the card measure.

[julesdude]

@lbendlin 

Thanks, so far that's enabled the card visual at least to appear.

Now the total displaying is incorrect. And if I drag the measure to form an extra column on my matrix table to see how this is being broken down by Building Name, some of the values are incorrect for some rows, and even if they were correct, they don't sum up altogether to reflect what the card visual value is showing.

              

This is incorrect because it should be a count of unique values in the [VOY_5005_Lease_DST.Tenant Reference] column of the VOY_5006_Lease_Unit_DST table in in the data model for each [Building Name] - our first column in the matrix table. So a unique count of these tenants should start for each Building Name.

It's the same story for the other card visuals. All I want is for them to reflect a sum of what is displayed in the related column. Total Valuation from the Last Valuation (Fund Currency) column of the table,  Total Annual Net Rent from the Annual Net Rent column and Total Occupancy from the Occupancy column.