Divide Prior to SumX

[jpaguiar]

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

[FreemanZ · Accepted solution]

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:

[jpaguiar]

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!

[tamerj1]

Hi @jpaguiar 
PLease try

Target	Factor 2 =
CALCULATE (
    [Target	Factor],
    'Table'[Driver] = "Running"
)

[tamerj1]

Hi @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]
        )
    )

[jpaguiar]

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!

[tamerj1 · Accepted solution]

@jpaguiar 
Yes you are right. Seems like the context transition effect of the SUMMARIZE function is creating a filter context issue that is not easy to understand. Replacing SUMMARIZE with GROUPBY solves the problem

[FreemanZ · Accepted solution]

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:

[jpaguiar]

Hi @FreemanZ,

It worked perfectly for both filtered and unfiltered views!
Superb, man! Thank you!