Forum Discussion

Brad_Roberts's avatar
Brad_Roberts
Frequent Visitor
3 years ago

Measure Total

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])
 

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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. 

  • Brad_Roberts's avatar
    Brad_Roberts
    Frequent Visitor
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Brad_Roberts
      Frequent Visitor
      3 years ago

      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.