Forum Discussion

Alex22's avatar
Alex22
Regular Visitor
9 years ago
Solved

Sum based on Different Column

Hi,

 

I have columns [Month] , [Item]  and [Revenue], I want to show column "Month wise Revenue" as shown below in single table

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Alex22

     

    To sum Revenue group on Month, you just need to use ALLEXCEPT() to ignore Item slicing in CALCULATE().

     

    You can create either a column or measure like below:

     

    =CALCULATE(SUM(Table[Revenue]),ALLEXCEPT(Table,Table[Month]))

     

    Regards,

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Monthwise Revenue should be added as a "New Column" and use this formula:

    Monthwise Revenue = CALCULATE(
	SUMX(ALLSELECTED(DemoTable), DemoTable[Revenue]),	
	DemoTable[Month] = EARLIER(DemoTable[Month])
)


    Where 'DemoTable' is your example table.

     

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Alex22

     

    To sum Revenue group on Month, you just need to use ALLEXCEPT() to ignore Item slicing in CALCULATE().

     

    You can create either a column or measure like below:

     

    =CALCULATE(SUM(Table[Revenue]),ALLEXCEPT(Table,Table[Month]))

     

    Regards,