Forum Discussion

jason_summit's avatar
jason_summit
Regular Visitor
9 years ago
Solved

group by custom column percent increase

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 ...
  • CahabaData's avatar
    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