How to have a measure summarize on a matrix visual?

[okternal]

Hello,

I've been scouring the forums and I've found a couple of answers to questions very similar to mine, but not quite the same. Their solutions don't work for me, but I may just be misunderstanding.

I have two measures:

LNetLeaseGoal = 
IF(
        ((([NextMonthBudOcc]) - SUM('Monthly Consolidated'[BudOcc])) * SUM('Property Attributes'[Total Units])) + (SUM(Operations[LeasesExpires]) - SUM(Operations[LeasesRenewal])) < 1,
        1,
        ((([NextMonthBudOcc]) - SUM('Monthly Consolidated'[BudOcc])) * SUM('Property Attributes'[Total Units])) + (SUM(Operations[LeasesExpires]) - SUM(Operations[LeasesRenewal]))
)

And

LULeadGoal = DIVIDE([LNetLeaseGoal], 0.064)

When I put it on a Matrix visual, I get this: 

LNetLeaseGoal is not summarized in the sub-total rows, which causes LULeadGoal to be incorrect as well. I need LNetLeaseGoal to be summarized in the subtotal rows. I've tried a few of the solutions out there, and one I got close, but for some reason it returned incorrect numbers in some cases. 

NewNetLeaseGoalTest = SUMX(Operations, [LNetLeaseGoal])

Any insight would be appreciated.

[v-yueyunzh-msft · Accepted solution]

Hi , @okternal 

According to your test , the total in your Matix is wrong. Right?

It is normal for an error to be in a measure because the global context is used when calculating Total.

You can try this way to modify the total:

(1)Use the SUMX() function to calculate your measure, but be careful that the table of the first argument should be kept as a table with your row headers.

(2)Replace the value of "Total" with the HASONEVALUE() function.

Modify total or need to combine the fields placed by your visual and your logical relationship to modify, if the above methods do not meet your needs, can you provide your .pbix file (remove sensitive data) to us, so that we can better help you.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[okternal]

Such a simple solution! Thanks so much.