Forum Discussion
Showing percentage calculation using multiple columns
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
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
6 Replies
- v-lili6-msftCommunity Support
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
- kennoeHelper I
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
- v-lili6-msftCommunity Support