DAX formula is not suitable

[esuryahadi]

Hello,
I have difficulty. I created a DAX formula like this.

SisaNilaiKontrak =

CALCULATE(

SUM('Contract'[Remaining Contract Value (USD)]) -

SUMX( FILTER( 'SPK Plan',

'SPK Plan'[No. Contract]

),

'SPK Plan'[Spk Plan ]

),

'Contract'[No Contract],

'SPK Plan'[Bulan]

and result like this :

However, the results are not appropriate.
the results in the "Remaining Contract Value" column should be the same as in the "Remaining USD Contract" column

what should I change to the formula?
Attached I share the link to this file

Thank you

https://drive.google.com/file/d/1oXdO-1hdSNzbpNH4brRZTkRyeSxxJ5zj/view?usp=sharing

[123abc · Accepted solution]

It seems like you are trying to calculate the remaining contract value by subtracting the sum of 'SPK Plan'[Spk Plan] from the sum of 'Contract'[Remaining Contract Value (USD)]. If the results are not as expected, you might want to check a few things:

1. Data Relationships: Ensure that there is a proper relationship between the 'Contract' table and the 'SPK Plan' table. The columns used in relationships should have unique values.

2. Filter Context: Make sure the context in which the formula is evaluated is what you expect. The columns used in CALCULATE and SUMX might be introducing unexpected filters. Check if the filters are applied correctly.

3. Aggregation Level: Verify if the aggregation level of your calculation is correct. If there are multiple rows with the same contract number in either table, your calculation might not be aggregating at the right level.

Without access to the actual data, it's challenging to provide a precise solution. I recommend breaking down your formula step by step, checking the intermediate results to identify where the discrepancy might be occurring.

Here's a simplified example of how you might structure your formula:

SisaNilaiKontrak =
CALCULATE(
SUM('Contract'[Remaining Contract Value (USD)]) -
CALCULATE(
SUM('SPK Plan'[Spk Plan]),
'SPK Plan'[No. Contract] = 'Contract'[No Contract],
'SPK Plan'[Bulan] = 'Contract'[Bulan]
)
)

This assumes a direct relationship between 'SPK Plan' and 'Contract' tables based on 'No Contract' and 'Bulan'. Adjust this according to your actual data model and relationships.

If you encounter specific issues, feel free to provide more details, and I'll do my best to assist you.

[Anonymous · Accepted solution]

Hi, @esuryahadi 

I'm happy to answer your questions. Your sumx may not be summing down. For example, the second line in the example image should be SUM('Contract'[Remaining Contract Value (USD)])-400-145-118.5175=306.13. The incorrect reason of getting 451.13 is that it does not subtract 145. Here I offer my solution，I created an index column inside the power query using the index column, Then we scroll sum through the index columns . For example, it  may understand your formula that Contract'[Remaining Contract Value (USD)] - SPK Plan'[Spk Plan ]. This Spk plan we have filtered. I will offer a possible solution using the data provided in your image:

After creating the index column, it looks like this:

Here is my DAX expression:

SisaNilaiKontrak = 
var _total = CALCULATE(SUM('Contract'[Remaining Contract Value (USD)]),'Contract'[No.contract],'Spk plan'[Bulan])
var _rolling = CALCULATE(SUM('Spk plan'[Spk Plan]),FILTER('SPK Plan','Spk plan'[Index]<=EARLIER('Spk plan'[Index])),FILTER('Spk plan','Spk plan'[No.Contract]),'Spk plan'[No.Contract],'Contract'[No.contract])
RETURN ABS(_total - _rolling) 

The results of the calculations are as follows：

You can create similar index columns to perform auxiliary calculations to achieve your requirements.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[123abc · Accepted solution]

It seems like you are trying to calculate the remaining contract value by subtracting the sum of 'SPK Plan'[Spk Plan] from the sum of 'Contract'[Remaining Contract Value (USD)]. If the results are not as expected, you might want to check a few things:

1. Data Relationships: Ensure that there is a proper relationship between the 'Contract' table and the 'SPK Plan' table. The columns used in relationships should have unique values.

2. Filter Context: Make sure the context in which the formula is evaluated is what you expect. The columns used in CALCULATE and SUMX might be introducing unexpected filters. Check if the filters are applied correctly.

3. Aggregation Level: Verify if the aggregation level of your calculation is correct. If there are multiple rows with the same contract number in either table, your calculation might not be aggregating at the right level.

Without access to the actual data, it's challenging to provide a precise solution. I recommend breaking down your formula step by step, checking the intermediate results to identify where the discrepancy might be occurring.

Here's a simplified example of how you might structure your formula:

SisaNilaiKontrak =
CALCULATE(
SUM('Contract'[Remaining Contract Value (USD)]) -
CALCULATE(
SUM('SPK Plan'[Spk Plan]),
'SPK Plan'[No. Contract] = 'Contract'[No Contract],
'SPK Plan'[Bulan] = 'Contract'[Bulan]
)
)

This assumes a direct relationship between 'SPK Plan' and 'Contract' tables based on 'No Contract' and 'Bulan'. Adjust this according to your actual data model and relationships.

If you encounter specific issues, feel free to provide more details, and I'll do my best to assist you.