Forum Discussion

motyagi's avatar
motyagi
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper I
8 years ago

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

Project Difference Balance
A42183149
B409813294
C10173559
D-2950

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's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
8 years ago

motyagi

 

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

 

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

Regards

Victor

  • motyagi's avatar
    motyagi
    Icon for Helper I rankHelper 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