Forum Discussion

msalahkar's avatar
msalahkar
Frequent Visitor
6 years ago

Adding a column with fixed value in matrix

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 sho...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Not sure I got it

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

 

msalahkar's avatar
msalahkar
Frequent Visitor
6 years ago

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's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      msalahkar
      Frequent Visitor
      6 years ago

      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_descriptionQTR1QTR2
      Gross Revenue2,027,1191,390,671
      OOP2,027,1191,390,671
      Car Allowance2,027,1191,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's avatar
        v-lionel-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.