Forum Discussion

kennoe's avatar
kennoe
Helper I
7 years ago
Solved

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 IdentifierPercent CompleteAmountExpense AmountForecast AmountVariance
C000510199%$151,644$156,171$3,0045%
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

6 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community 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