Forum Discussion
Creating a new table to fix a calculation
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'
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
8 Replies
- 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.
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])))
