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.
I want to run the cumulative running sum/total in table visual.
Here Fiscal month is a column and Monthly Comm Earned is a measure which includes another measure also.
now I want to calculate the sum of the running total in the sales running column.
like march will have only march amount and in April contains march + April amount and so on..
Monthly Comm Earned =
IF(
ISFILTERED(Transactions[Fiscal Month]),
[MonthlyCommEarnedCalc],
SUMX(
VALUES(Transactions[Fiscal Month]), [MonthlyCommEarnedCalc]
)
)
I Tried the below query but not working.
Sales Running =
Var MaxMonth = Max(Transactions[Fiscal Month Nbr])
Var Result = CALCULATE(
Transactions[Monthly Comm Earned] ,
Transactions[Fiscal Month nbr]<= MaxMonth,
All(Transactions[Fiscal Month Nbr])
)
Return Result
April month should return value 8560.98 in sales running column/measure.
Solved! Go to Solution.
Finally, I got the solution to calculate the running total for measures without a date column.
To calculate the running total for another measure then just add that measure code ( if block ) into your running total code.
example code :
Monthly Comm running sum =
VAR month = MAX(Transactions[Fiscal Month Nbr])
VAR result = CALCULATE(
IF( ISFILTERED(Transactions[Fiscal Month]),
[MonthlyCommEarnedCalc],
SUMX(VALUES(Transactions[Fiscal Month]), [MonthlyCommEarnedCalc])
),
Transactions[Fiscal Month] <= month,
ALLSELECTED(Transactions)
)
Return result
Finally, I got the solution to calculate the running total for measures without a date column.
To calculate the running total for another measure then just add that measure code ( if block ) into your running total code.
example code :
Monthly Comm running sum =
VAR month = MAX(Transactions[Fiscal Month Nbr])
VAR result = CALCULATE(
IF( ISFILTERED(Transactions[Fiscal Month]),
[MonthlyCommEarnedCalc],
SUMX(VALUES(Transactions[Fiscal Month]), [MonthlyCommEarnedCalc])
),
Transactions[Fiscal Month] <= month,
ALLSELECTED(Transactions)
)
Return result
@MrSujay You will need something that defines "before", like a fiscal year/month column 202101, 202102, etc. Then you can do something like:
Measure =
VAR __Current = MAX('Transactions[Fiscal Month nbr])
VAR __Table = FILTER(ALL('Table'),[Fiscal Month nbr] <= __Current)
RETURN
SUMX(__Table,[Monthly Comm Earned])
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 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |