Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
Is there a way to add a nested calculation within a running total?
Example:
When the running total hits 1,000 subtract 900 and continue the running total...
Would it better to try this as a measure or a calculated column?
Thank you.
Solved! Go to Solution.
you can update the DAX like below
Column =
VAR _SUM=SUMX(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[Daily])
VAR _reach=int(_SUM/1000)
VAR _result=_SUM-_reach*900
return if (_result>1000,_result-900,_result)
Proud to be a Super User!
agree with @PhilipTreacy , measure is better.
you can try his solution
if you want to create a column, you can try this
Column =
VAR _SUM=SUMX(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[Daily])
VAR _reach=int(_SUM/1000)
return _SUM-_reach*900
Proud to be a Super User!
Hey Guys,
I tried it both ways and a couple issues - both work for the first trigger.
As a measure:
Question 1 - is there any logic I can add behind the measure to calculate off the previous days result instead of SUM?
As a calcualted column:
Question 2 - Is there logic behind calculating the integer and rounding it up somehow? This seems like the more comlplex solution though.
I need to take a mental break from this so here is some sample data if anyone wants to take a crack at it. Thank you!
Date | Daily | SUM | reach_INT | Test1 (column) | Test2 (measure) | CORRECT | Trigger | Decrease |
9/26/2022 | 105 | 105 | 0 | 105 | 105 | 105 | 1000 | 900 |
9/27/2022 | 100 | 205 | 0 | 205 | 205 | 205 | ||
9/28/2022 | 125 | 330 | 0 | 330 | 330 | 330 | ||
9/29/2022 | 150 | 480 | 0 | 480 | 480 | 480 | ||
9/30/2022 | 115 | 595 | 0 | 595 | 595 | 595 | ||
10/1/2022 | 160 | 755 | 0 | 755 | 755 | 755 | ||
10/2/2022 | 110 | 865 | 0 | 865 | 865 | 865 | ||
10/3/2022 | 200 | 1065 | 1 | 165 | 165 | 165 | ||
10/4/2022 | 100 | 1165 | 1 | 265 | 265 | 265 | ||
10/5/2022 | 205 | 1370 | 1 | 470 | 470 | 470 | ||
10/6/2022 | 180 | 1550 | 1 | 650 | 650 | 650 | ||
10/7/2022 | 110 | 1660 | 1 | 760 | 760 | 760 | ||
10/8/2022 | 190 | 1850 | 1 | 950 | 950 | 950 | ||
10/9/2022 | 170 | 2020 | 2 | 220 | 1120 | 220 | ||
10/10/2022 | 180 | 2200 | 2 | 400 | 1300 | 400 | ||
10/11/2022 | 195 | 2395 | 2 | 595 | 1495 | 595 | ||
10/12/2022 | 115 | 2510 | 2 | 710 | 1610 | 710 | ||
10/13/2022 | 115 | 2625 | 2 | 825 | 1725 | 825 | ||
10/14/2022 | 160 | 2785 | 2 | 985 | 1885 | 985 | ||
10/15/2022 | 110 | 2895 | 2 | 1095 | 1995 | 195 | ||
10/16/2022 | 200 | 3095 | 3 | 395 | 2195 | 395 | ||
10/17/2022 | 100 | 3195 | 3 | 495 | 2295 | 495 | ||
10/18/2022 | 205 | 3400 | 3 | 700 | 2500 | 700 | ||
10/19/2022 | 180 | 3580 | 3 | 880 | 2680 | 880 | ||
10/20/2022 | 110 | 3690 | 3 | 990 | 2790 | 990 | ||
10/21/2022 | 190 | 3880 | 3 | 1180 | 2980 | 280 | ||
10/22/2022 | 180 | 4060 | 4 | 460 | 3160 | 460 | ||
10/23/2022 | 110 | 4170 | 4 | 570 | 3270 | 570 | ||
10/24/2022 | 190 | 4360 | 4 | 760 | 3460 | 760 | ||
10/25/2022 | 170 | 4530 | 4 | 930 | 3630 | 930 | ||
10/26/2022 | 160 | 4690 | 4 | 1090 | 3790 | 190 | ||
10/27/2022 | 110 | 4800 | 4 | 1200 | 3900 | 300 |
you can update the DAX like below
Column =
VAR _SUM=SUMX(FILTER('Table','Table'[date]<=EARLIER('Table'[date])),'Table'[Daily])
VAR _reach=int(_SUM/1000)
VAR _result=_SUM-_reach*900
return if (_result>1000,_result-900,_result)
Proud to be a Super User!
Works beautifully. Thank you both!
you are welcome
Proud to be a Super User!
Hi @kpickle
Yes. Let's say you are calcuting the RT like this (a measure, this is better than a static calculated column)
= CALCULATE(SUM([Actual]), FILTER(ALLSELECTED('Calendar'[Date]),ISONORAFTER('Calendar'[Date], MAX(Actual[Date]), DESC)))
You can modify that to check the value of the running total (storing it in a Variable called _RT) and carry out whatever modification you want based on the value of _RT e.g.
= VAR _RT = CALCULATE(SUM([Actual]), FILTER(ALLSELECTED('Calendar'[Date]),ISONORAFTER('Calendar'[Date], MAX(Actual[Date]), DESC)))
RETURN
IF(_RT > 1000, _RT - 1000, _RT)
Regards
Phil
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |