Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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]))
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))
Create a new measure:
New=[ValueMeasure] - SUM(Table[Hours])
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.
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?
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:
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.
Can you post a screenshot of your data sample and the measures that you using.
I test the Dax previous and works fine
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 |
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
week | hours | Measure | New Measure |
1 | 202 | 442 | -15846608 |
2 | 140 | 442 | -15859228 |
3 | 458 | 442 | -15879377 |
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
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]))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
84 | |
76 | |
49 |
User | Count |
---|---|
143 | |
141 | |
109 | |
69 | |
55 |