Forum Discussion

Patelnitya's avatar
Patelnitya
Frequent Visitor
10 years ago

Help with DAX formula

Hello Power BI Users, 

 

I am having trouble solving the below issue. 

 

I have megered two tables - One is a SQL Sales Data from my erp system and the other is an excel costing file

 

My aim is to get:

 

1) Gross Profit per product (Which i have managed to create a new column and calculate) works accurately: 1-Product GP = vwNityaSalesView2[1-Ave.P]-vwNityaSalesView2[1-Cost Aug 16']

 

2) Total Gross Profit - I am having Problems with this one: I have tried the following formula "1-Total GP = vwNityaSalesView2[1-Product GP]*vwNityaSalesView2[QtySold]"  but i get an error "A circular dependency was detected: vwNityaSalesView2[1-Product GP], vwNityaSalesView2[1-Total GP], vwNityaSalesView2[1-Product GP]."

 

Attached images for better understanding. 

 

Kind Regards, 

Nitya

6 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    10 years ago

    Hi Nitya,

     

    Have you tried SUMX that I suggested you before.

     

    Thanks & Regards,

    Bhavesh

    • Patelnitya's avatar
      Patelnitya
      Frequent Visitor
      10 years ago

      Hi Bhavesh, 

       

      I did try SUMX. However the figures are not coming correct. See below screen shot. e.g. Product Code K007. I think the issue is that 1-Product GP is Average. 

       

      Any ideas to solving this. 

       

      rgds

       

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    Patelnitya

     

    I don't get the circular dependency problem when trying to add those two columns following the snapshot. Can you upload the pbix for further troubleshooting?

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        9 years ago

        Patelnitya

         

        Try to change the [1-Ave.P] to

         

         

        1-Ave.P =
SUMX ( ALL ( vwNityaSalesView2 ), vwNityaSalesView2[Value Excl] )
    / SUMX ( ALL ( vwNityaSalesView2 ), vwNityaSalesView2[QtySold] )

        Then the cricular error in below calculated column shoud go away.

         

         

        1-Total GP = vwNityaSalesView2[1-Product GP]*vwNityaSalesView2[QtySold]

         

         

        Or keep the measure [1-Ave.P] as it is, change

         

        1-Product GP =
        SUMX ( ALL ( vwNityaSalesView2 ), vwNityaSalesView2[Value Excl] )
            / SUMX ( ALL ( vwNityaSalesView2 ), vwNityaSalesView2[QtySold] )
            - vwNityaSalesView2[1-Cost Aug 16']

         

         

        Check Understanding Circular Dependencies.