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