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,

 

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.

      • 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