Help with DAX formula

[Patelnitya]

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

[Eric_Zhang]

@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?

[Patelnitya]

Hello All, 

Pls find data sample in the below link for your better understanding of the problem. 

https://www.dropbox.com/s/ldx691r9xzkyx58/Papa%20Sep%202016%20-%20Vr1%20-%20Copy.pbix?dl=0

Thanks, 

Nitya 

[Eric_Zhang]

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

[Patelnitya]

Hello Eric, 

I am still having some DAX formulations with your proposed solutions. 

Attached are some screen shots of what you proposed. Notice that Ave Price is the same throughout all rows and on the second option the calculations are not correct . 

[BhaveshPatel]

Hi Nitya,

Have you tried SUMX that I suggested you before.

Thanks & Regards,

Bhavesh

[Patelnitya]

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