Forum Discussion
Divide Prior to SumX
hi jpaguiar
try like:
Column =VAR _table=FILTER(TableName,TableName[Cycle]=EARLIER(TableName[Cycle]))VAR _running =SUMX(FILTER(_table,TableName[Driver]="Running"),TableName[Actual])VAR _waiting =SUMX(FILTER(_table,TableName[Driver]="Waiting"),TableName[Actual])RETURN DIVIDE(_running, _waiting)it worked like:
Hi there FreemanZ tamerj1!
You guys have helped me a lot to progress with the data transformation steps that I am having to do in order to create an interactive dashboard, however I got into a new roadblock.
If you have the chance, could you please take at this new challenge?
| Driver | Actual | Target | Cycle |
| Running | 120 | 100 | A |
| Jogging | 50 | 40 | A |
| Waiting | 30 | 10 | A |
| Running | 140 | 110 | B |
| Jogging | 25 | 30 | B |
| Waiting | 12 | 20 | B |
I was able to use the proposed solutions to summarize the original table to this aggragated one on which I don't need to specificy the metrics for each Cycle.
| Driver | Actual | Target | Delta | R_Actual | R_Target | Diff Target | Factor | R_Delta |
| Running | 130.0 | 105.0 | 25.0 | 112.0 | 140.0 | 35.0 | 0.333333 | 8.33 |
| Jogging | 37.5 | 35.0 | 2.5 | 41.0 | 40.0 | 0.83 | ||
| Waiting | 21.0 | 15.0 | 6.0 | 27.0 | 25.0 | 2.00 |
From the original Actual and Target values, I re-calculated them after some data transformation to get R_Actual and R_Target. Then I calculated a measure that is the difference between R_Target and Target but only for Driver = "Running". Then I created another measure (Factor) so that I could use this factor for re-calculating the data column proportionaly (R_Delta). My issue is that I am unable to pass the factor 0.33 resulting from the measure Factor to all rows to then multiply it by the Target column values for all rows to get 0.83 and 2.00. PowerBI does the multiplication row by row, therefore it shows empty cells for R_Delta for Jogging and Waiting Rows.
Thank you so much in advance!
