Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
aktripathi2506
Helper IV
Helper IV

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

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:

 

WeekHoursNew
1253175
2483127
33862741
43742367
56881679
63521327
7534793
8365428
9164264
10295-31

 

Thanks & Regards,

A

2 ACCEPTED SOLUTIONS
jahida
Impactful Individual
Impactful Individual

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]))

View solution in original post

After seeing his data; the key is that have weeks with negatives values, so the measure was modified to:

 

MeasureToSubstractions = [Measure]-CALCULATE(Sum(test[Hours]);FILTER(ALLEXCEPT(test;Test[Year];test[Report Week]);test2[Week]<=max(test[Week]) && test[Week]>0))




Lima - Peru

View solution in original post

25 REPLIES 25
Vvelarde
Community Champion
Community Champion

Create a new measure:

 

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




Lima - Peru

Not working.

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

this is what I am getting as a new value:

 

weekhoursMeasureNew Value
1202442-32599025.85
2140442-32599025.85
3458442-48898538.77
4279442-48898538.77
7466442-65198051.69
8393442-48898538.77
9183442-16299512.92
10366442-16299512.92
11415442-65198051.69
1267442-16299512.92
13309442-32599025.85
1488442-32599025.85
15130442-32599025.85
1669442-16299512.92
17109442-32599025.85
19159442-16299512.92
2037442-16299512.92
2134442-16299512.92
2236442-16299512.92
2341442-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.

 

Hi,

 

This works on my machine:

 

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

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.

 

 

Your questions are fun to solve 🙂

 

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

jahida
Impactful Individual
Impactful Individual

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.

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.

 

Can you take a screenshot maybe?

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.

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

Now the error is:

 

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

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.

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:

 

weekhoursMeasureNew Measure
1202442-15846608
2140442-15859228
3458442-15879377
4279442-15907761
7465.6442-16011141.53
8392.8571429442-16042508.39
9182.6666667442-16074054.39
10365.8181818442-16103519.48
11415.3846154442-16130239.64
1266.66666667442-16154108.57
13308.9411765442-16175251.04
1487.57894737442-16194748.83
15130.1904762442-16211854.45
1668.7826087442-16226405.67
17108.88442-16239779.91
19159.2413793442-16258581.23
2036.58064516442-16264863.75
2134.36363636442-16270201.14
2236442-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.

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

 

I test the Dax previous and works fine

 

 




Lima - Peru

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:

 

weekmeasurehoursnew MeasureExpected output
1442202240202
244214030262
3442458-16-396
4442279163-675
5442465.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 Weekweekhours
1112
1214
1118
1410
1220
1830
1925
2115
2212
2918
2620
2222
2425

 

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:

 

weekhoursmeasureexpected output
13010070
23410036
41010026
830100-4
925100-29

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.

 

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 

 

weekhoursMeasureNew Measure
1202442-15846608
2140442-15859228
3458442-15879377

 

@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..).

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
Impactful Individual
Impactful Individual

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]))

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.