Subtracting a cell value from a another cell (measure) in a single column

[aktripathi2506]

HI,

I have the following scenario:

In the visual I am summarising the weekly data.

So I have week then next column I have is hours, which is basically the sum of hours for the week.

I have a measure which has certain value.

Now I want the third column having ongoing subtraction:

If the value of measure is 3200 then value of column 'new' should be as follows:

Week	Hours	New
1	25	3175
2	48	3127
3	386	2741
4	374	2367
5	688	1679
6	352	1327
7	534	793
8	365	428
9	164	264
10	295	-31

Thanks & Regards,

A

[jahida · Accepted solution]

New version of the formula:

Measure = 500 - MINX(test, SUMX(FILTER(ALL(test), test[Week] <= EARLIER(test[Week]) && test[Week] > 0 && test[Report Week] = EARLIER(test[Report Week])), test[Hours]))

[Vvelarde]

Create a new measure:

New=[ValueMeasure] - SUM(Table[Hours])

[aktripathi2506]

Not working.

Tried your suggestion: New Value = Measure- SUM('Table'[hours])

this is what I am getting as a new value:

week	hours	Measure	New Value
1	202	442	-32599025.85
2	140	442	-32599025.85
3	458	442	-48898538.77
4	279	442	-48898538.77
7	466	442	-65198051.69
8	393	442	-48898538.77
9	183	442	-16299512.92
10	366	442	-16299512.92
11	415	442	-65198051.69
12	67	442	-16299512.92
13	309	442	-32599025.85
14	88	442	-32599025.85
15	130	442	-32599025.85
16	69	442	-16299512.92
17	109	442	-32599025.85
19	159	442	-16299512.92
20	37	442	-16299512.92
21	34	442	-16299512.92
22	36	442	-16299512.92
23	41	442	-16299512.92

Something we are missing, may be need to apply some filter in the formula as it is reading entire table. But I am not able to identify why.

As in the same table we can see both the values...values of hours and measure and then we are just subtracting them but the result we are getting is far different.

Thanks in advance.

[jahida]

Hi,

This works on my machine:

Measure 2 = [Measure] - MINX(Table, SUMX(FILTER(ALL(Table), Table[Week] <= EARLIER(Table[Week])), Table[Hours]))

[aktripathi2506]

Thank you @jahida

You helped me last time, this time I am getting a syntex error with the formula you suggested:

Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2.

I tried to modify then I was getting following error:

Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.

Sorry I am not very good with these formula.

Thank you for your time and efforts.

[jahida]

Your questions are fun to solve 🙂

Uh, that's strange... does your system use semi-colons instead of commas or something?

[jahida]

Sorry, try this:

Measure 2 = [Measure] - MINX('Table', SUMX(FILTER(ALL('Table'), 'Table'[Week] <= EARLIER('Table'[Week])), 'Table'[Hours]))

For some reason those single quotes weren't there and I think are necessary.

[aktripathi2506]

Glad to know that you like challanges 🙂

Sorry my mistake earlier i was putting the formula on new column than new measure.

But this time new problem occured: when I write the formulam, when I reach to 'EARLIER' it is not allowing me to write any thing meaning what ever i write it is giving me error.

Although when I comple writing the formula it is giving me following error:

Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2.

[jahida]

Can you take a screenshot maybe?

[aktripathi2506]

This is exactly what I am tying as a new measure. and yes my system accept commas.

Measure2 = [measure]-MINX('Table',SUMX(FILTER(ALL('Table','Table'[Week] <= EARLIER('Table'[Week])),'Table'[Hours])))

getting red color warning on bold area.

Also in my case hours is a new calculated column which I created using two other columns, but it should not make any difference.

[jahida]

There should be a close bracket immediately after "      ALL('Table'       ". And then I think just one fewer close bracket at the end.

[aktripathi2506]

Now the error is:

Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.

[jahida]

Measure2 = [measure]-MINX('Table',SUMX(FILTER(ALL('Table'),'Table'[Week] <= EARLIER('Table'[Week])),'Table'[Hours]))

I made the change I mentioned above, that should be exactly the formula. Filter has 2 arguments here so there's no reason it should complain.

[aktripathi2506]

Hey @jahida, sorry for the delayed reply, I had to leave due to some call.

Finally the equation worked...with no error but result is still unexpected:

this is what I am getting:

week	hours	Measure	New Measure
1	202	442	-15846608
2	140	442	-15859228
3	458	442	-15879377
4	279	442	-15907761
7	465.6	442	-16011141.53
8	392.8571429	442	-16042508.39
9	182.6666667	442	-16074054.39
10	365.8181818	442	-16103519.48
11	415.3846154	442	-16130239.64
12	66.66666667	442	-16154108.57
13	308.9411765	442	-16175251.04
14	87.57894737	442	-16194748.83
15	130.1904762	442	-16211854.45
16	68.7826087	442	-16226405.67
17	108.88	442	-16239779.91
19	159.2413793	442	-16258581.23
20	36.58064516	442	-16264863.75
21	34.36363636	442	-16270201.14
22	36	442	-16274939.77

Very strange too me...I also tried few things but I was getting similar kind of output so far from the desired one.

[Vvelarde]

Can you post a screenshot of your data sample and the measures that you using.

I test the Dax previous and works fine

[aktripathi2506]

Hi @jahida and @Vvelarde,

O changed my equation to this:

Measure2 = [measure] - MINX('Table',SUMX(FILTER('Table','Table'[week]<= EARLIER('Table'[week])),'Table'[hours]))

basically removed 'All'

and then this is what i am getting:

week	measure	hours	new Measure	Expected output
1	442	202	240	202
2	442	140	302	62
3	442	458	-16	-396
4	442	279	163	-675
5	442	465.6	-23.6	-1140.6

I also tried 

[measure]- CALCULATE(SUM('Table'[hours]),FILTER('Table','Table'[Index] <=EARLIER('Table'[Index])))

but it was giving some error like:

The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

May be I should explain my data in more detail:

Screen shot will not help, it will look complicated..so I am giving example here:

Reporting Week	week	hours
1	1	12
1	2	14
1	1	18
1	4	10
1	2	20
1	8	30
1	9	25
2	1	15
2	2	12
2	9	18
2	6	20
2	2	22
2	4	25

This is how the raw data look like,

Sorry I forgot to mention that there is repeatation of week, may be thats why we are getting the bigger value because it is summing them all.

Actualy I have a slicer for reporting week, based on that I filter all the week belonging to that report week only. But may be measure is summing them all? @jahida

Based on the raw table posed above, the expected output should like this following:

If report week slicer is set to 1 then for report week 1:

week	hours	measure	expected output
1	30	100	70
2	34	100	36
4	10	100	26
8	30	100	-4
9	25	100	-29

[jahida]

The Measure will definitely not give the expected result without some sort of All function. I didn't realize you were using slicers in the report, in that case you should use an ALLEXCEPT function probably. Replace the ALL in my orginial function with:

ALLEXCEPT('Table', 'Table'[Reporting Week])

I think the measure should still work despite the repetition of the week field, even within each reporting week.

[aktripathi2506]

Used this formula:

New Measure = [measure] - MINX('table',SUMX(FILTER(ALLEXCEPT('table', table[report week]),'table'[week]<= EARLIER('table'[week])),'table'[hours]))

Still getting those long values 

week	hours	Measure	New Measure
1	202	442	-15846608
2	140	442	-15859228
3	458	442	-15879377

[aktripathi2506]

@jahida

I have a lot of historical data from the year 2013. So there are same reporting week for each year.

But I have  slicer for year and reporting week.

I thought by selecting the year and reporting week from the slicer will solve the purpose.

But when I filter the year and reporting week again in visual level filter than I see values changing from   -15,839,752 to -440,265 (reduced, obviously it is still too big number but filtering reduced it)

So is there anything need to do with year and week as well in the formula of measure.

Also for each reporting week, weeks are not in a particular order…they are random (ex. 1, 5, 3, 7, 5, 9, 2, 4 )… reporting weeks are in sequence (ex. 1, 2, 3, 4, 5, 6..).

[jahida]

Any fields that you expect to filter on, add to the end of the ALLEXCEPT statement. Other than that, should be fine. Sharing the pbix isn't a bad idea

[jahida · Accepted solution]

New version of the formula:

Measure = 500 - MINX(test, SUMX(FILTER(ALL(test), test[Week] <= EARLIER(test[Week]) && test[Week] > 0 && test[Report Week] = EARLIER(test[Report Week])), test[Hours]))