Calculate Margin Based On Classification

[tksnota]

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

[tksnota]

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

[tksnota]

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

[Anonymous]

Hi @tksnota ,

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.

[tksnota]

Hi,

The above DAX seems to work.

Please help me with this one. 

gbkmut_pns_drd = Revenue

artcode = Classification

bdr_hfl = Revenue Amount

Revenue = gbkmut_pns_drd[bdr_hfl]

Thanks...tksnota

[Anonymous · Accepted solution]

Hi @tksnota ,

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]

Hi @tksnota ,

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.

[tksnota]

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