Sum of Measures Returning Incorrect Value

[Anonymous]

Hi Everyone,

I'm stumped on this problem I'm having with a measure that is not adding up. 

I found this article on Measure Totals which helped me better understand the difference between row context and filter context in DAX, but I'm finding it difficult to apply the example solution to my problem.

As you can see, the total is not summing correctly.

 This is the equation I'm using to create the MeasureProfit values.

 Any idea what I can do to receive a correct sum for the MeasureProfit caluclation?  All other columns are working as expected.

Thank You!

[TomMartens]

Hey @Anonymous,

as @Greg_Deckler already mentioned, without knowing what's the "correct" result should look like, and telling us why, it's very difficult to provide an answer.

So this is also just guesswork ...

Whats's going on:

Each row is a substantial part of the filter context (in addition to slicers, that are there or not), except the Total Row. For this reason, I assume that the Sum (meaning over all rows visible in the table visual) of "MeasureTotalCost" is deducted from the MIN('AllData'[Retainer].

I guess that a Measure like this will solve your Problem, maybe it could be necessary to use AVERAGEX instead of MINX as the table iterator function

MeasureProfit = 
MINX(
ALLSELECTED('AllData')
,MIN('AllData'[Retainer] - [MeasureTotalCost]
)

Hope this gives you an idea.

Maybe you should consider to create a pbix file with sampledata, upload the file to onedrive or dropbox, and share the link with us.

Regards

Tom

[Anonymous]

All,

I'm attaching the .pbix with test data as @TomMartens recommended.

To view an example of the issue from the Report view, filter by month (January and February) and select Client 3

It is located here:  https://1drv.ms/f/s!Av7yMH1V07x8ogYw68C5JOz-ltPH

I understand it is difficult to give an answer without it.

Thank you for your support.

[Greg_Deckler]

@Anonymous- Thanks for providing the PBIX file. I guess, can you give us a specific circumstance such as filtering by month (January and February and then selecting Client 3) as to what is the correct value that you would expect to see. In other words, under those circumstances, what value should be displayed in the Total line for MeasureValue?

[Anonymous]

A few examples:

Selecting Client3 and date range from January to end of February, the value on the total line for MeasureProfit should be $2,700.00

Selecting Client8 and date range from February to end of March, the value on the total line for MeasureProfit should be $1,300.00

Selecting Client9 and date range from May to end of June, the value on the total line for MeasureProfit should be $6,000.00 

[TomMartens · Accepted solution]

Hey,

Measure seems to create the specified results:

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

Here you will a pbix file 

Here are some screenshots

Hope this is what you are looking for
Tom

[Anonymous]

@TomMartens this is what I was looking for!  Thank You.

Can you give more information on why you used the "This" statements?

[TomMartens]

Hey,

„This“ is just a the name for the new column introduced by the Table function ADDCOLUMN and finally used by the function SUMX.

I always use „funny“ names to aboid confusion with the names of real columns and measures. So, „This“ could be replaced with „Sebastian“ and the statement will still work 🙂

Regards Tom

[GIO200011]

It depends what you want to do. I suspect is the sum of MeasureProfit of each day. I don't know that the MeasureTotalCost do, so it's difficult to guide you.

Add this to your measure:

MeasureProfit = IF(HASONEVALUE(Date),MIN(AllData[Retainer])-[MeasureTotalCost],XXXXX)

Instead of "XXXXX", insert the measure you need. If you provide us more information, we can help you more.

Regards,

-Giovany

[Greg_Deckler]

So what should teh correct value be?