Forum Discussion
group by custom column percent increase
- 9 years ago
Here is one way to do it. It requires a series of steps: (I call your example the 'IncreaseExample' table)
Data level
1. create a table: IncreaseIDs = VALUES(IncreaseExample[ID])
.... this is a table with just the unique IDs list, a single column
2. join IncreaseIDs to your table on the ID field
3. In this new IncreaseIDs table; create a calculated column:
IDScore = CALCULATE((SUM(IncreaseExample[Score])))
Visuals - assuming a table visual
4. create Table visual using columns from Increase Example - undo the default and select normal dates per your example
5. create new measure
ICalc1 = SUMX(IncreaseExample,RELATED(IncreaseIDs[idscore])-[Score])
6. create new measure
ICalc2 = SUMX(IncreaseExample,[Score]-[ICalc1])
7. create new measure
IPct = SUMX(IncreaseExample,
IF([ICalc2]>0,[ICalc2]/[ICalc1],0))IPct measure is the final - you will want to option that to display as a Percentage
ICalc1 and ICalc2 do not need to display in the final visual
Thank you for the response. Looking at this however it may not accomplish what I need. I think it is my fault the way I displayed the data. I need the increase to calculate based on clientid and date field of the data.
ClientId Date Score
1 10/1/2015 40
1 05/4/2016 100
3 1/1/2016 30
3 6/7/2016 40
5 3/12/2016 60
5 6/20/2016 80
But then change the output to look like this.
ClientId %Change
1
3
5
it essentially works; as posted it will show all records, thus the 1 record of an ID will have 0%
you can overlay a filter for the 0s if you just want the rows with increases