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 befo...
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.

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