Calculate average with condition without changing layout of input data

[ngocnguyen]

Hi all.

I have a raw data as table 1.   So. if I wanna calculate the Round 2 vs Round 1 of Route A, I need to change layout of table 1 to table 2 in power query and create a calculated column " Round 2 vs Round 1"  ( =DIVIDE(Table1[Round 2],Table1[Round 1],0)  )

Logic to calculate Round 2 vs Round 1 of Route A as below:

= CALCULATE(AVERAGE([Round 2 vs Round 1]),FILTER(Table2,[Route]="A",[Round 2 vs Round 1]<>0))

So, is there any way that I don't need to change layout of table 1 to table 2 and still get the correct result of Round 2 vs Round 1  of Route A ?

Thanks

[Syndicate_Admin]

Don't @ngocnguyen,

Create these measures:

Average 2vs1 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "2 vs 1", DIVIDE ( 'Table'[2], 'Table'[1], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [2 vs 1] <> 0 ), [2 vs 1] )

Average 3vs2 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "3 vs 2", DIVIDE ( 'Table'[3], 'Table'[2], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [3 vs 2] <> 0 ), [3 vs 2] )

Best regards
Community Support Team _ Yingjie Li
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Don't @ngocnguyen,

Create these measures:

Average 2vs1 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "2 vs 1", DIVIDE ( 'Table'[2], 'Table'[1], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [2 vs 1] <> 0 ), [2 vs 1] )

Average 3vs2 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "3 vs 2", DIVIDE ( 'Table'[3], 'Table'[2], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [3 vs 2] <> 0 ), [3 vs 2] )

Best regards
Community Support Team _ Yingjie Li
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Don't @ngocnguyen,

Create these measures:

Average 2vs1 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "2 vs 1", DIVIDE ( 'Table'[2], 'Table'[1], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [2 vs 1] <> 0 ), [2 vs 1] )

Average 3vs2 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "3 vs 2", DIVIDE ( 'Table'[3], 'Table'[2], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [3 vs 2] <> 0 ), [3 vs 2] )

Best regards
Community Support Team _ Yingjie Li
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Don't @ngocnguyen,

Create these measures:

Average 2vs1 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "2 vs 1", DIVIDE ( 'Table'[2], 'Table'[1], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [2 vs 1] <> 0 ), [2 vs 1] )

Average 3vs2 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "3 vs 2", DIVIDE ( 'Table'[3], 'Table'[2], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [3 vs 2] <> 0 ), [3 vs 2] )

Best regards
Community Support Team _ Yingjie Li
If this post helps, consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Don't @ngocnguyen,

Create these measures:

Average 2vs1 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "2 vs 1", DIVIDE ( 'Table'[2], 'Table'[1], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [2 vs 1] <> 0 ), [2 vs 1] )

Average 3vs2 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "3 vs 2", DIVIDE ( 'Table'[3], 'Table'[2], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [3 vs 2] <> 0 ), [3 vs 2] )

Best regards
Community Support Team _ Yingjie Li
If this post helps,then consider Accepting it as the solution to help other members find it faster.

[v-yingjl]

Hi @ngocnguyen ,

Create these measures:

Average 2vs1 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "2 vs 1", DIVIDE ( 'Table'[2], 'Table'[1], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [2 vs 1] <> 0 ), [2 vs 1] )

Average 3vs2 = 
VAR tab =
    ADDCOLUMNS ( 'Table', "3 vs 2", DIVIDE ( 'Table'[3], 'Table'[2], 0 ) )
RETURN
    AVERAGEX ( FILTER ( tab, [3 vs 2] <> 0 ), [3 vs 2] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ngocnguyen]

@v-yingjl 

Is there any way that I can add slicer Round in your PBI file? and still get result?

[v-yingjl · Accepted solution]

Hi @ngocnguyen ,

Create a calculated table like this, use it as a 'Round' slicer:

Round Table =
UNION (
    ROW ( "Round", "Round 1" ),
    ROW ( "Round", "Round 2" ),
    ROW ( "Round", "Round 3" )
)

Keep the previous measure and create a new measure:

Average =
SWITCH (
    SELECTEDVALUE ( 'Round Table'[Round] ),
    "Round 1", BLANK (),
    "Round 2", [Average 2vs1],
    "Round 3", [Average 3vs2]
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ngocnguyen]

@v-yingjl 

Sincerely thanks so much for your support. 

You're so amazing!.!!.

[v-yingjl]

Hi @ngocnguyen ,

I'm afraid not if use the same logic.

When you choose 'M' and 'Round 3', the table would filter these values.

Same logic would just get the blank value becasue the round and route value is static not dynamic in the logic.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ngocnguyen]

@v-yingjl . 

So, how about if I convert data to below table ? is there any way to create  2 slicers as I mentioned as above?