Adding a column with fixed value in matrix

[msalahkar]

Hi, 

I am creating a P&L in matrix and along with the amount column i want to add a percentage column which divides row value with total revenue. therefore I want to calculate a measure which shows revenue on each row regardless of the column row slicer. Here is a percentage which shows percentage with account head. I want to add a percentage column as (account subcategory/net revenue). Please help

Can anyone help please

[Anonymous]

Suppose you have:

>> A=reshape(1:16,4,4)

A =

1 5 9 13

2 6 10 14

3 7 11 15

4 8 12 16

>> B=(17:20)'

B =

17

18

19

20

Then you could obtain the desired matrix C by:

>> C = [A(:,1:2) B A(:,3:4)]

C =

1 5 17 9 13

2 6 18 10 14

3 7 19 11 15

4 8 20 12 16

So you take the first two columns of A concatenate the column B and then concatenate the last two columns of A.

You can generalize this a bit into:

>> D = [A(:,1:N) B A(:,N+1:end)]

Where N then stands for "insert B after the Nth column".

[amitchandak]

Not sure I got it

Calculate([Net Revenue], all(Table))

[msalahkar]

hi @amitchandak and @Anonymous 

just to make my question clearer, here is what i tried

AllRevenue = SUMX(FILTER(Chart_of_Accounts,Chart_of_Accounts[AccountHead] = "Net Revenue"),SUMX(ALL(General_Ledger_Entries[Correct Amount]),General_Ledger_Entries[Correct Amount]))

It gives following result

 It is accumulating all revenues for whole period and showing it same value in all quarters. What i need is that it accumulates Revenue for the quarter and displays the same in all rows in its respective quarter. I hope I am able to explain clearly and you guys can help. Thanks 

[v-lionel-msft]

Hi @msalahkar ,

Do you want this result? (My columns are from the same table.)

Measure 3 = 
CALCULATE(
    SUM('Sales 2015'[Value]),
    ALL('Sales 2015'[Brand]),   //Remove filter on [Brand] column
    ALLEXCEPT(
        'Sales 2015',
        'Sales 2015'[CountryRegion], 'Sales 2015'[Month] // Group and sum by [CountryRegion] and [Month]
    )
)

Maybe you can show an example data model composed of these two tables and the results you want, we can compare the two to see how to calculate.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msalahkar]

hi @v-lionel-msft 

Here is the table which from which I am filtering the data. The values are in other table and it is sliced by this table.

Now I tried your formmula but it doesnt work. Here is how its showing now.

basically in QTR 1, i need 2,027,119 in all field of Account_subcategory_description. similarly in QTR2, I need 1,390,671 in all fileds of Account_subcategory_description. like this

Account_subcategory_description	QTR1	QTR2
Gross Revenue	2,027,119	1,390,671
OOP	2,027,119	1,390,671
Car Allowance	2,027,119	1,390,671

for Allrevenue2 column i am using this measure

All Revenue2 = CALCULATE(SUM(General_Ledger_Entries[Correct Amount]),FILTER(Chart_of_Accounts,Chart_of_Accounts[AccountHead] = "Net Revenue"),ALL(Chart_of_Accounts[AccountHead]))

Thanks

[v-lionel-msft]

Hi @msalahkar ,

My understanding is that you need to ignore the filters on [Account Head] column and [Account_Subcategory_Descript] column and sum by [Fiscal Quarter] column, is that right?

Try this measure again:

Measure 2 = 
CALCULATE(
    SUM(General_Ledger_Entries[Correct Amount),
    ALL(Chart_of_Accounts,Chart_of_Accounts[AccountHead],Chart_of_Accounts,Chart_of_Accounts[Account_Subcategory_Descript]),
    ALLEXCEPT(
        General_Ledger_Entries,
        General_Ledger_Entries[Fiscal Quarter]
    )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msalahkar]

hi @v-lionel-msft 

Thanks for your effort but that is not working as well. I guess what I am asking for might not be possible at the moment in power BI.

Thanks for your help though!!