group by custom column percent increase

[jason_summit]

I am working with some health related data and I am trying to build a DAX query to give me precent increase by client id and date on the table lines.  Any suggestion on how to transform this data to show client id and +- %Change?

Thank you in advance for your help.

The data looks like the following

ClientId               Date                Score               %Increase??

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

[CahabaData · Accepted solution]

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

[jason_summit]

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                                   

[CahabaData]

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