Measure Total

[Brad_Roberts]

I'm new to DAX and have tried every solution I could find to calculate the correct total for Move-Out without any luck.  All the suggested solutions always returned the same -1,411 as below. 

Change SF is a measure of (Sum of All Area - Start SF).  Start SF is a measure = calculate(sum('Tenancy Data'[All Area]),all(Dates),USERELATIONSHIP(Dates[Date],'Inactive Dates'[Start Date]))

Here is the table:

My measure (which works) for:   Move-In SF =

sumx(

    filter(

        summarize(

            values('Tenancy Data'[Tcode]),'Tenancy Data'[Tcode],"change SF",'Tenancy Data'[Change SF]),'Tenancy Data'[Change SF]>0),'Tenancy Data'[Change SF])

For some reason that measure works for move-in but not for move-out, all I do is change ">" to "<" but no luck, same result as Test Move-out values.

I came across a solution where you split the sumx function into a new measure.  However, that gives me the solution only for one row, which also is the only row with a number in Sum of All Area.

Test Move-Out = if([Change SF] < 0, [Change SF])

Test Move-Out values  = sumx(values('Tenancy Data'[Tcode]),[Test Move-out])

[Anonymous]

Hi  @Brad_Roberts ,

Are you referring to the incorrect Total value of the yellow label.

You can create a Measure, use HASONEVALUE and IF to make judgments, and make sure Total is correct.

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create measure.

Total =
var _table1=SUMMARIZE('Table', 'Table'[Index],"Value",[Test Move-out])
return
IF(
    HASONEVALUE('Table'[Index] ),
   [Test Move-out],SUMX(_table1,[Value]))

3. Result:

Best Regards,

Liu Yang

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

[Brad_Roberts]

Liu Yang,

When I go to add the index, not all of my rows get a value.  Start SF is measure based off of an inactive relationship.  This seems to be my issue, all the DAX functions I have tried don't recongize those rows.

[Brad_Roberts]

I'm trying to create two measures that total Move-in and Move-out square footage (Change SF).  The Move-in measure works, totals the Change SF >0.  However, the same measure doesn't work for Move-Outs, Change SF < 0, that should total to 36,657.

[Ashish_Mathur]

Hi,

Please describe the question in simple English.  What is SF?  What are you trying to do?  By explaining the question/giving a business perspective, you are likely to get varied and better solution.  Show the expected result as well.

Share the download link of the PBI file.