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.
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.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |