Measure not summing correctly for Total Row

[Anonymous]

Hi All,

This is driving me crazy. I reffered to this post 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] > 0, [Project Profit] , 0))

Loss = CALCULATE(IF([Project Profit] < 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.

[amitchandak]

Please try

Profit = CALCULATE(sum([Project Profit]) ,[Project Profit] > 0)
Loss = CALCULATE(sum([Project Profit]) ,[Project Profit] < 0)

OR

Profit = CALCULATE(([Project Profit]) ,[Project Profit] > 0)
Loss = CALCULATE(([Project Profit]) ,[Project Profit] < 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]

Hi @amitchandak  sorry but you cannot use true/false condition inside calculate. Also we can not sum measure explicitly.  The below dax by @TomMartens  was quite close but still not giving the expected result. 

MeasureProfitFinal = 
CALCULATE( 
    SUMX(
        ADDCOLUMNS(
        SUMMARIZE('AllData'
            ,'AllData'[Client]
            ,'AllData'[Date].[Month]
            ,'AllData'[Retainer]
        )
        ,"This",[MeasureProfit])
        ,[This])
    )

 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]

Ok, profit is a calculated measure 

try this, something like

Sales GT 0 = CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Current Sales]>0))
or

Sales GT 0 = CALCULATE((Sales[Sales Amount]),filter(sales,Sales[Current Sales]>0))

[Anonymous]

Hi @amitchandak  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:

Project Profit =

CALCULATE(TOTALYTD(SUMX('Loss Factor', 'Loss Factor'[Project Profit] * [Budget Rate]), 'Calendar'[Date], ALL('Calendar')))

Profit = CALCULATE(IF([Project Profit] > 0, [Project Profit] , 0)) --> old formula

Loss = CALCULATE(IF([Project Profit] < 0, [Project Profit], 0 ))

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 dax

ProfitFinal =

CALCULATE(

SUMX(

ADDCOLUMNS(

SUMMARIZE('Loss Factor'

,'Loss Factor'[Project]

)

,"This",[Profit])

,[This])

) --> new formula

But with this i'm not getting YTD number for project and that's why sum is not matching with value expected.

@Ashish_Mathur can you please assist me as i have seen you have answered similar posts.

[Ashish_Mathur]

Hi,

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

[Anonymous]

Hi All,

Thanks for all your help and time. While looking for solution, i came across this post which helped me in getting the expected result. So on top of the existing Profit and Loss measures which i made as intermediate, i created another 2 measures as shown below and got the desired result.

Profit  = IF(HASONEVALUE('Loss Factor'[Project Profit]), [Profit Intermediate],TOTALYTD(SUMX(VALUES('Loss Factor'[Project]), [Profit Intermediate]),'Calendar'[Date]))

Loss =

IF(HASONEVALUE('Loss Factor'[Project Profit]), [Loss Intermediate],TOTALYTD(SUMX(VALUES('Loss Factor'[Project]), [Loss Intermediate]),'Calendar'[Date])).

Cheers