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 to calculate a percentage amount based on data from three different tables. I have used this formula:
"divide(sum('Actual Expense'[Expense Amount])+sum('Forecast Expense'[Forecast Amount]),sum('Initiative Funding'[Amount]))"
and converted the outcome to a percentage.
The problem is this gives me an amount like 105% or 97%, instead of 5% or -3% that I want to show.
In excel, I would simply subtract 1 from the result of the formula, so I tried:
"divide(sum('Actual Expense'[Expense Amount])+sum('Forecast Expense'[Forecast Amount]),sum('Initiative Funding'[Amount])-value(1))" and while it worked for the lines I wanted, it also subtracted 1 from all the other data. See example below:
Initiative Identifier | Percent Complete | Amount | Expense Amount | Forecast Amount | Variance |
C0005101 | 99% | $151,644 | $156,171 | $3,004 | 5% |
C0005101 | -100% | ||||
C0005101 | -100% | ||||
C0005101 | -100% | ||||
C0005101 | -100% | ||||
C0005101 | -100% | ||||
C0005101 | -100% |
This pattern is repeated for the other six projects in the data.
What is wrong with my formula?
Steve
Solved! Go to Solution.
hi, @kennoe
After my test, it should be the reason that there are a lot of fields from different tables in one visual,
there is a simple way to solve it.
add a measure
filter blank = DIVIDE(SUM(AE_Example41[Expense Amount])+SUM(FE_Example41[Forecast Amount]),SUM(IF_Example41[Amount]))
and drag it into visual level filter of the table filter and set the filter like this:
Result:
here is pbix, please try it.
Best Regards,
Lin
hi,@kennoe
I have tested on my side, but not reproduce the issue. It seems that there are complex relationships between them
and what is "Percent Complete" a column? a mesure? we need more detail steps for us.
Please share your sample pbix for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
I have tested the data a number of times and get the same result each time. The data has been uploaded to DropBox and can be found here:
https://www.dropbox.com/preview/Public/Example41.pbix?role=personal
Steve
hi, @kennoe
Your link seems to be invalid, please upload again.
Best Regards,
Lin
hi, @kennoe
After my test, it should be the reason that there are a lot of fields from different tables in one visual,
there is a simple way to solve it.
add a measure
filter blank = DIVIDE(SUM(AE_Example41[Expense Amount])+SUM(FE_Example41[Forecast Amount]),SUM(IF_Example41[Amount]))
and drag it into visual level filter of the table filter and set the filter like this:
Result:
here is pbix, please try it.
Best Regards,
Lin
Thank you for this solution. For a novice like me, can you please explain how this resolves the problem?
Steve
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 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |