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 ,

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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

 

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's avatar
    Anonymous
    Not applicable
    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.