Forum Discussion
Solved
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 goa...
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
Community ChampionHi jpaguiar
Please refer to attached sample file with the proposed solution
Measure =
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!