Forum Discussion
AnetaK
Helper V
5 years agoRunning Total (based on project size)
Hello, I need help in creating a Running Total measure, but what is important - it should be based on projects, not dates. Usually, Running Total is created by date, we start in January and increas...
DataInsights
Super User
5 years ago
Try this solution.
1. Create measures:
Total Cost = SUM ( FactProject[Cost] )
Cost % =
VAR vNumerator = [Total Cost]
VAR vDenominator =
CALCULATE ( [Total Cost], ALLSELECTED () )
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Running Total =
VAR vTopNumProjects = 10
--get all projects and their Cost % per the slicers/filters
VAR vBaseTable =
ADDCOLUMNS (
SUMMARIZE ( ALLSELECTED ( FactProject ), FactProject[Project] ),
"@Cost%", [Cost %]
)
--get the top N projects
VAR vTopProjects =
TOPN ( vTopNumProjects, vBaseTable, [@Cost%] )
--add rank to the top N projects
VAR vRankedProjects =
ADDCOLUMNS ( vTopProjects, "@Rank", RANKX ( vTopProjects, [@Cost%],, DESC ) )
--get the current project in the visual
VAR vCurrentProject =
MAX ( FactProject[Project] )
--get the row in vRankedProjects for the current project
VAR vCurrentProjectRow =
FILTER ( vRankedProjects, FactProject[Project] = vCurrentProject )
--get the rank for the current project
VAR vCurrentRank =
MAXX ( vCurrentProjectRow, [@Rank] )
--get the rows that have a rank <= the current project's rank
VAR vTargetRows =
FILTER ( vRankedProjects, [@Rank] <= vCurrentRank )
VAR vResult =
IF ( HASONEVALUE ( FactProject[Project] ), SUMX ( vTargetRows, [@Cost%] ), BLANK() )
RETURN
vResult
2. Create visual. Set the following visual filter: [Running Total] > 0. Sort the visual by [Running Total], ascending.