Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Brad_Roberts
Frequent Visitor

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:

 

Brad_Roberts_1-1671506164783.png

 

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 4
v-yangliu-msft
Community Support
Community Support

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.

vyangliumsft_0-1671609263195.png

Here are the steps you can follow:

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

vyangliumsft_1-1671609263197.png

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:

vyangliumsft_2-1671609263198.png

 

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

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_0-1671635167030.pngBrad_Roberts_1-1671635205624.png

 

Brad_Roberts
Frequent Visitor

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
Super User
Super User

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. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors