Showing percentage calculation using multiple columns

[kennoe]

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

[v-lili6-msft]

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

[kennoe]

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-msft]

hi, @kennoe

    Your link seems to be invalid, please upload again.

Best Regards,

Lin

[kennoe]

Sorry, try this:

https://www.dropbox.com/s/p9lfizbzmy5yu2z/Example41.pbix?dl=0

Steve

[v-lili6-msft · Accepted 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.

https://www.dropbox.com/s/poq6lz5j8ez26uy/Showing%20percentage%20calculation%20using%20multiple%20co...

Best Regards,

Lin

[kennoe]

Thank you for this solution. For a novice like me, can you please explain how this resolves the problem?

Steve