The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a formula that almost gives me what I want. My current calculation looks for the last expected contract amount and brings that back for all Year/Months. I'm struggling to get it to bring back a zero if the expected contract amount is zero. When I try to add an IF(SUM(Expected Contract Amt.) >0, 0) it creates a circular reference.
Here are the two formulas i'm trying:
Hi,
Try use this return for your LECA measure:
Return IF(SUM('IFD&FRR'[Expected Contract Amt])=BLANK(),0,secECA)
Paul
Best
Hi Paul, thanks for your response. I've tried that formula, but it gives me the circular dependency error. I've added the calculated column formulas that the circular dependency is referencing. Maybe I have to change something with that so I don't get this error and then I can use the If(SUM(...
I created a column for Cur Ttl Cost Fcst and True % Complete because I couldn't figure out a measure to use for dividing by zero
@Jdokken
I agree with you that if you sort out error related to the 3 calculated columns, then if(sum will work. If I can have the pbix files, I am sure I can help you out with this troubleshooting.
Paul
The 3 calculated columns I included are what links to the LECA & LGMF. I need the LECA (Last Expected Contract Amount) and LGMF to calculate the Cur Ttl Cost Fcst. I need the Cur Ttl Cost Fcst to calculate the True % Complete. And I need the True % Complete to calculate whether the Contract Value is >.05 or <.95.
Is there anything else I can provide that would be helpful? I can't provide the PBIX file as the data is confidential.
@Jdokken
I really need to know the what are the columns in your tables and the relationship. You can create a sample pbix using random values if possible.
Paul
The underlying problem was that I couldn't include a CALCULATE(SUM(... see in blue below, because I have calculated columns that are attached to that data. The following formula works if I delete the three Calculated columns I change them to measures but I can't get the True % Complete measure to work correctly. I think my best bet would be to recreate the following measure into a calculated column. Would you know how to do that? Otherwise, I can look into creating a sample PBIX.
Sorry for the delay. I have a sample PBIX file. How do I send it to you? As a recap, in the file, it is bringing back the Last ECA and Last GMF, but when the GMF or ECA is zero I need it to bring back zero for that row but the Last entered amount for the other dates. Let me know if you have questions.
You can upload to OneDrive for Business and share the link here.
Paul Zheng
@Jdokken
Sorry I don't check pm. And I don't have the right to access the file, maybe something share setting is limited. Try enable "every one with this link can see" for your file.
Paul
If you have an email address, I could send it there.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |