Forum Discussion

motyagi's avatar
motyagi
Helper I
8 years ago
Solved

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 Thr...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

     

    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

motyagi's avatar
motyagi
Helper I
8 years ago

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 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

 

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