Measure not summing correctly for Total Row

Question

Hi All,&nbsp;This is driving me crazy. I reffered to this post&nbsp;by Greg_Deckler. Created YTD measure for Project Profit column present in Loss Factor table. Created 2 separate measures Profit and Loss with below calculation:Project Profit = CALCULATE(TOTALYTD(SUMX('Loss Factor', 'Loss Factor'[Project Profit] * [Budget Rate]), 'Calendar'[Date], ALL('Calendar')))Profit = CALCULATE(IF([Project Profit] &gt; 0, [Project Profit] , 0))Loss = CALCULATE(IF([Project Profit] &lt; 0, [Project Profit], 0 ))Now when i'm dragging them in table visual the Profit is showing same value as Project Profit which is what it is doing as per calcualtion and being evaluated in current context but i'm expecting it to give different result like individual values summed up by project as shown in below screenshot, want to change the context tried ALL, ALLEXCEPT but no luck.&nbsp;

amitchandak · Answer

Please tryProfit = CALCULATE(sum([Project Profit]) ,[Project Profit] &gt; 0)
Loss = CALCULATE(sum([Project Profit]) ,[Project Profit] &lt; 0)

OR

Profit = CALCULATE(([Project Profit]) ,[Project Profit] &gt; 0)
Loss = CALCULATE(([Project Profit]) ,[Project Profit] &lt; 0)Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @ Thanks.

anonymous · Answer

Hi&nbsp;amitchandak&nbsp; sorry but you cannot use true/false condition inside calculate. Also we can not sum measure explicitly.&nbsp; The below dax by&nbsp;TomMartens&nbsp; was quite close but still not giving the expected result.&nbsp;MeasureProfitFinal = 
CALCULATE( 
    SUMX(
        ADDCOLUMNS(
        SUMMARIZE('AllData'
            ,'AllData'[Client]
            ,'AllData'[Date].[Month]
            ,'AllData'[Retainer]
        )
        ,"This",[MeasureProfit])
        ,[This])
    )&nbsp;Please help me as this is bit urgent and not able to proceed further. When i export the data to the excel i get the correct result but not in Total row in table visual.

amitchandak · Answer

Ok, profit is a calculated measure&nbsp;try this, something like&nbsp;Sales GT 0 = CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Current Sales]&gt;0))
or

Sales GT 0 = CALCULATE((Sales[Sales Amount]),filter(sales,Sales[Current Sales]&gt;0))&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;amitchandak&nbsp; please refer to my situation below:I have column project profit in my table Loss Factor. I have created measure to get YTD values for Project Profit as below:&nbsp;Project Profit =CALCULATE(TOTALYTD(SUMX('Loss Factor', 'Loss Factor'[Project Profit] * [Budget Rate]), 'Calendar'[Date], ALL('Calendar')))Profit = CALCULATE(IF([Project Profit] &gt; 0, [Project Profit] , 0)) --&gt; old formulaLoss = CALCULATE(IF([Project Profit] &lt; 0, [Project Profit], 0 ))&nbsp;Now the problem is Profit is always coming same as Project profit because of the calculation, but what i want is to show row wise Total summed for project. Hope it helps. Somewhere i got close to the solution using below daxProfitFinal = CALCULATE( SUMX(ADDCOLUMNS(SUMMARIZE('Loss Factor','Loss Factor'[Project]),"This",[Profit]),[This])) --&gt; new formulaBut with this i'm not getting YTD number for project and that's why sum is not matching with value expected.Ashish_Mathur&nbsp;can you please assist me as i have seen you have answered similar posts.

ashish_mathur · Answer

Hi,
I am not clear with your question.&nbsp; Share some data, explain the question and show the expected result.

Forum Discussion

Measure not summing correctly for Total Row

6 Replies

Related content

Negative values not be summed correctly

Measure totals not summing correctly

DAX measure does not sum value correctly

Complex measure not summing correctly in matrix visual, nor by year

DAX Measure not summing correctly