Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculate Margin Based On Classification

Hi,

 

I need to create a table to show Project's Margin (Cost/Revenue) based on matching Classifications using Table 1 & 2. One exception is Personnel and Personnel2 in Revenue should be added before dividing the Cost's Personnel.

 

If nothing matched and blanks then Margin should show a note - Compare to Other Classification

 

Thanks...

 

tksnota

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    As I mentioned in my reply above, based on your screenshot it's easy to see that you still haven't returned the variable you defined.

    The error was generated because there was no RETURN. Here is the modified DAX statement:

    Margin% =
VAR PersonnelTotal =
    SUMMARIZE (
        FILTER (
            'gbkmut_pns_drd',
            'gbkmut_pns_drd'[artcode] IN { "PERSONNEL", "PERSONNEL2" }
        ),
        "Classification", "PersonnelTotal",
        "Revenue", SUM ( gbkmut_pns_drd[bdr_hfl] )
    )
RETURN
    PersonnelTotal

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

    I create two tables as you mentioned.

    Then I create a new table and here is the DAX code.

    Column = 'Revenue'[Revenue]
    Table = 
VAR PersonnelTotal = 
    SUMMARIZE(
        FILTER(
            'Revenue',
            'Revenue'[Classification] IN {"Personnel", "Personnel2"}
        ),
        "Classification", "PersonnelTotal",
        "Revenue", SUM('Revenue'[Revenue])
    )
VAR OtherRows = 
    FILTER(
        'Revenue',
        NOT 'Revenue'[Classification] IN {"Personnel", "Personnel2"}
    )
RETURN
    UNION(
        ADDCOLUMNS(
            ROW("Classification", "PersonnelTotal", "Revenue", SUMX(PersonnelTotal, [Revenue])),
            "Revenue1", SUMX(PersonnelTotal, [Revenue])
        ),
        OtherRows
    )

    Finally I create a calculated column and get what you want.

    Column = 
VAR _Revenue1 = 'Table'[Revenue1]
VAR _Cost =
    RELATED ( Cost[Cost] )
RETURN
    IF (
        'Table'[Classification] = RELATED ( Cost[Classification] ),
        _Cost / _Revenue1,
        BLANK ()
    )

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi,

       

      Thanks for the effort. I'm still trying out the DAX that you shared.

       

      In the meantime, can you create a DAX to add the difference? 

       

      I need to show the percentage and difference based on classification.

       

      Thanks...tksnota

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

     

    I tried using a part of the DAX and I'm getting this error as shown.

     

    I couldn't establish relationship because of the error

    FYI. 

    gbkmut_pns_drd = Revenue
    artcode = Classification

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

       

      i tried  writing the dax manually and get this different error.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous ,

        Based on the error reported on the image you provided it is easy to see that you are not returning any value after defining the variable. You can use RETURN to return the variable you defined.

        So you can try the DAX code below.

        Measure = 
VAR OtherRows =
    FILTER(
        'pbixnav_gns_ord',
        NOT ('pbixnav_gns_ord'[artCode] IN {"PERSONNEL", "PERSONNEL2"})
    )
RETURN
    OtherRows

         

         

        Best Regards

        Yilong Zhou

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.