Adding a column with fixed value in matrix

Question

Hi,&nbsp;&nbsp;I am creating a P&amp;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&nbsp;Can anyone help please&nbsp;

v-lionel-msft · Answer

Hi&nbsp;msalahkar ,&nbsp;Do you want this result? (My columns are from the same table.)&nbsp;&nbsp;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]
    )
)&nbsp;&nbsp;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.&nbsp;Best regards,Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

amitchandak · Answer

Not sure I got it
Calculate([Net Revenue], all(Table))
&nbsp;

anonymous · Answer

Suppose you have:&nbsp;&gt;&gt; A=reshape(1:16,4,4)&nbsp;A =&nbsp;1 5 9 132 6 10 143 7 11 154 8 12 16&gt;&gt; B=(17:20)'&nbsp;B =&nbsp;17181920Then you could obtain the desired matrix C by:&nbsp;&gt;&gt; C = [A(:,1:2) B A(:,3:4)]&nbsp;C =&nbsp;1 5 17 9 132 6 18 10 143 7 19 11 154 8 20 12 16So 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:&nbsp;&gt;&gt; D = [A(:,1:N) B A(:,N+1:end)]Where N then stands for "insert B after the Nth column".&nbsp;

msalahkar · Answer

hi&nbsp;amitchandak&nbsp;and&nbsp;Anonymous&nbsp;&nbsp;just to make my question clearer, here is what i tried&nbsp;AllRevenue = SUMX(FILTER(Chart_of_Accounts,Chart_of_Accounts[AccountHead] = "Net Revenue"),SUMX(ALL(General_Ledger_Entries[Correct Amount]),General_Ledger_Entries[Correct Amount]))&nbsp;It gives following result&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

msalahkar · Answer

hi&nbsp;v-lionel-msft&nbsp;&nbsp;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.&nbsp;&nbsp;&nbsp;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&nbsp;Account_subcategory_description. like this&nbsp;Account_subcategory_descriptionQTR1QTR2Gross Revenue2,027,1191,390,671OOP2,027,1191,390,671Car Allowance2,027,1191,390,671&nbsp;for Allrevenue2 column i am using this measure&nbsp;All Revenue2 = CALCULATE(SUM(General_Ledger_Entries[Correct Amount]),FILTER(Chart_of_Accounts,Chart_of_Accounts[AccountHead] = "Net Revenue"),ALL(Chart_of_Accounts[AccountHead]))&nbsp;Thanks

Forum Discussion

Adding a column with fixed value in matrix

7 Replies

Related content

Adding columns

Adding a column

Adding Calculated Column to Matrix - based on Column values

Adding a Static column to a matrix visual of measures

Renaming columns and adding columns