P&L statement with matrix - % of revenue

[JNcr]

Hi, I have created a P&L statement using the matrix visual.  

I have one table containing the actual results and another table containing the P&L template.

In the matrix I would like to include a column with each line item as a % of total revenue, however the formula I use only works next to the total revenue line, see below.

My formula is 

Actuals % = DIVIDE( [Actuals (£'000)] , [Total revenue (£'000)] , 0)

I know I need to override the filters in the table I just don't know how to do this.

The other relevant measures are as follows:

Actuals (£'000) =   VAR 

Actual = FILTER(Accounts, Accounts[Actual / budget] = "Actual")

RETURN

CALCULATE([Values £'000] , Actual)

Total revenue (£'000) = CALCULATE( [Actuals (£'000)] ,

FILTER(Accounts , Accounts[Source] = "Actual") , FILTER(Accounts , Accounts[Name] = "Revenue" ))

Values £'000 = ROUND( DIVIDE( SUM(Accounts[Values] ) , 1000 , 0) , 0)

[v-lili6-msft · Accepted solution]

hi, @JNcr 

You need to adjust the Total revenue (£'000) by ALL Function or ALLSELECTED Function as below:

Total revenue (£'000) = CALCULATE( [Actuals (£'000)] ,
FILTER(ALL(Accounts) , Accounts[Source] = "Actual") , FILTER(ALL(Accounts) , Accounts[Name] = "Revenue" ))

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

If not your case, please share your sample pbix file and expected output.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[JNcr]

Thanks Lin, a great help