Forum Discussion
Divide Prior to SumX
Hi folks,
It might a simple task, but I've tried to create the Aux_Column using several different combinations of Calculate, SumX, Divide, Filter, FirstNonBlank, among others but no lucky.
My goal is to have for each IDs in the 'Cycle' column the result of 'Running' / 'Waiting' rows of the 'Driver' column using values from the 'Actual' column. For instance, for Cycle = A, all rows should be 4 (120/30), while for Cycle = B they should be 11.67 (140/12), etc. The issue is that the Cycle column is present only in the raw data, not in the summary table in the visual, therefore when aggregating values for the Aux_Column, most formulas that I've tried are performing 120 + 140 and then dividing by 30 + 12.
Thanks in advance for any help.
| Driver | Actual | Cycle | Aux_Column |
| Running | 120 | A | 4 |
| Jogging | 50 | A | 4 |
| Waiting | 30 | A | 4 |
| Running | 140 | B | 11.67 |
| Jogging | 25 | B | 11.67 |
| Waiting | 12 | B | 11.67 |
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:
7 Replies
FreemanZSuper User
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:- tamerj1
Community Champion
Hi jpaguiar
Please refer to attached sample file with the proposed solutionMeasure = VAR T1 = CALCULATETABLE ( 'Table', ALLSELECTED ( 'Table' ), VALUES ( 'Table'[Cycle] ) ) VAR T2 = SUMMARIZE ( T1, 'Table'[Driver], "@SUM", SUM ( 'Table'[Actual] ) ) RETURN PRODUCTX ( T2, SWITCH ( 'Table'[Driver], "Running", [@SUM], "Waiting", 1/[@SUM] ) )Hi tamerj1,
It worked great with no filters applied, but then when I filter either cycle A or cycle B, it apparently tries to calculate the division for each 'Driver' individualy, resulting in 'Infitiny'. If I try to use the same strategy but using a calculated column, it again works for unfiltered views but returns 1.04 for either Cycle = A or Cycle = B filters, which I believe is just calculing the sum prior to the division again.
FreemanZ worked fine for both filtered and unfiltered.
Thank you for the support mate!
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!
