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 all,
I have an issue calculating running totals.
We use a matching chance metric to calculate a forecast.
Matching chance Job level = SUMX(VALUES(Table[column1]) ; CALCULATE(MAX(Table[column2])))
Now we want to calculate running totals on the level of account and date (year/month). And we made this DAX formula:
Running Total = CALCULATE(SUM(Table1[columnA]); FILTER(ALL(Table1); Table1[Date] <= MAX(Table1[Date])); VALUES(Table2[columnA]); VALUES(Table1[columnB]))
Year | Month | Matching chance Job level | Running Total |
2016 | October | 1 | 1,00 |
2016 | November | 1,4 | 2,00 |
2016 | December | 0,4 | 1,90 |
2017 | January | 1 | 3,00 |
2017 | February | 0,5 | 3,10 |
TOTAL | 4,40 | 4,40 |
Any ideas would be much appericated. Thanks for any and all help in advance!
Solved! Go to Solution.
Running Total = CALCULATE(SUM(Table1[columnA]); FILTER(ALL(Table1); Table1[Date] <= MAX(Table1[Date])),FILTER(ALLSELECTED(Table2[columnA]),Table2[columnA]=Table2[columnA]),FILTER(ALLSELECTED(Table1[columnB]),Table1[columnB]=Table1[columnB]))
Then try this way in order to make filter works
Formula Looks good not sure if VALUES are helping out in this case. Try remove those and see if you are able to do calculations as desired.
Running Total = CALCULATE(SUM(Table1[columnA]); FILTER(ALL(Table1); Table1[Date] <= MAX(Table1[Date])))
Thanks for your response @FarhanAhmed1984. I think I need to include the VALUES because I want to filter my visual based on Account (name) and Position (status). Or do you see another option?
Running Total = CALCULATE(SUM(Table1[columnA]); FILTER(ALL(Table1); Table1[Date] <= MAX(Table1[Date])),FILTER(ALLSELECTED(Table2[columnA]),Table2[columnA]=Table2[columnA]),FILTER(ALLSELECTED(Table1[columnB]),Table1[columnB]=Table1[columnB]))
Then try this way in order to make filter works
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |