Creating a new table to fix a calculation

[motyagi]

Hi There, 

I am trying to fix a calculation to the Project Type and create a new column - the end  goal is to check 

If  ABS([$ Difference ])>sum([EP Balance Comparison])
then 'Out of Threshold'
else 'Within Threshold' END

Fixed at a project type detail. 

I want to used this new created field to color points on my scatter plot. Red for 'Out of Threshold'

[motyagi]

@Vvelarde Can you help me with this one as well?

[motyagi]

I am trying to create a new table that would pull dat from here - 

Project	Difference	Balance
A	4218	3149
B	4098	13294
C	1017	3559
D	-29	50

As mentioned Difference is a measure created with other fields so the new table doesnt have the same values Balance on the other end matches perfectly. 

I want to create a field that will mark A as out of threshold and i want to use this field to color the scatter plot dot for A as red and all the other projects and green. 

I created the new table using this  - 

Color = SUMMARIZE('Sample Upload File','Sample Upload File'[Project Type],
"Diff",[$Difference_final],"Balance Conversion",CALCULATE(SUM('Non-Invoiceable CY by Project Type'[Balance Comparison]),All('Sample Upload File'[Project Type])))

[Vvelarde]

@motyagi

You can add a new calculated column to this new table:

Column = IF(ABS(Table1[Difference ])>Table1[Balance];"Out";"Within")

Regards

Victor

[motyagi]

But my problem is that when I pull the measure from the old table to a new table ( to fx this by project type) it gives me a different value 

[v-shex-msft · Accepted solution]

HI @motyagi,

For your requirement ,you can use var function to store this summary table into measure formula.

If you need to lookup specific value from that table, I'd like to suggest write other variables to get current row contents, then use these variables as filter parameters on above table and use try to summary them with single value.

Sample:

Measure=
VAR _current = Table2[Type]
VAR temp =
    ADDCOLUMNS (
        SUMMARIZE (
            'Sample Upload File',
            'Sample Upload File'[Project Type],
            "Diff", [$Difference_final],
            "Balance Conversion", CALCULATE (
                SUM ( 'Non-Invoiceable CY by Project Type'[Balance Comparison] ),
                ALL ( 'Sample Upload File'[Project Type] )
            )
        ),
        "Result", IF ( ABS ( [Difference ] ) > [Balance], "Out", "Within" )
    )
RETURN
    MAXX ( FILTER ( temp, [Project Type] = _current ), [Result] )

Regards,
Xiaoxin Sheng

[Vvelarde]

@motyagi

Hi, Are columns? or have a measure?

[motyagi]

Hi$Difference is a measure and balance conversion is a column so when I bring in the balance conversion it matches up but the measure doesn’t.

[Vvelarde]

@motyagi

Please post a sample to easily recreate this.