Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
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
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']
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 .
Hi Nitya,
Have you tried SUMX that I suggested you before.
Thanks & Regards,
Bhavesh
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
119 | |
105 | |
78 | |
74 | |
52 |
User | Count |
---|---|
145 | |
108 | |
107 | |
90 | |
64 |