cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

 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

2 ACCEPTED SOLUTIONS
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]))

Community Champion

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
25 REPLIES 25
Community Champion

Create a new measure:

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

Lima - Peru
Helper IV

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.

Impactful Individual

Hi,

This works on my machine:

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

Helper IV

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.

Impactful Individual

Your questions are fun to solve 🙂

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

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.

Helper IV

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.

Impactful Individual

Can you take a screenshot maybe?

Helper IV

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.

Impactful Individual

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

Helper IV

Now the error is:

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

Impactful Individual

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.

Helper IV

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.

Community Champion

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
Helper IV

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

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.

Helper IV

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

Helper IV

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

Impactful Individual

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors