Solved: Calculate average with condition without changing ...

ngocnguyen · ‎04-03-2021

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

v-yingjl · ‎04-06-2021

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.

View solution in original post

AllisonKennedy · ‎04-04-2021

@ngocnguyen This can all be done as measures in DAX:

Round 2 v Round 1 Avg=

AVERAGEX ( VALUES( Table1[Route1] ) , IF ( MIN(Table1[Round] ) <> 0, CALCULATE( DIVIDE ( SUM( Table1[Round2]) , SUM(Table1[Round1] ) ) ) ) )

Check the brackets and you may need to tweak that formula a bit - ideally you would have a dimension table fro Route1 values that we could use to average over.... do you have such a thing?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

ngocnguyen · ‎04-05-2021

@AllisonKennedy

Round 2 v Round 1 Avg=

AVERAGEX ( VALUES( Table1[Route1] ) , IF ( MIN(Table1[Round] ) <> 0, CALCULATE( DIVIDE ( SUM( Table1[Round2]) , SUM(Table1[Round1] ) ) ) ) )

You create a Round 2 column in Table 1?. cus in the original version, Table 1 don't have any column name Round 2

AllisonKennedy · ‎04-05-2021

@ngocnguyen Sorry, yes, you can use a filter instead:

Round 2 v Round 1 Avg=

AVERAGEX ( VALUES( Table1[Route1] ) , IF ( MIN(Table1[Round] ) <> 0, CALCULATE( DIVIDE ( SUMX( FILTER( Table1, Table1[Round] = "Round 2" ), Table1[Rate]) , SUMX ( FILTER ( Table1, Table1[Round] = "Round 1"), Table1[Rate] ) ) ) ) )

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

v-yingjl · ‎04-04-2021

Hi @ngocnguyen ,

About calculating Round 2 vs Round 1, you can create this measure:

Round 2 vs Round 1 = 
VAR round1 =
    CALCULATE ( SUM ( 'Table 1'[Rate] ), 'Table 1'[Round] = "Round 1" )
VAR round2 =
    CALCULATE ( SUM ( 'Table 1'[Rate] ), 'Table 1'[Round] = "Round 2" )
RETURN
    CALCULATE (
        DIVIDE ( round2, round1, 0 ),
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[Route]
                IN DISTINCT ( 'Table 1'[Route] )
                    && 'Table 1'[Route1] IN DISTINCT ( 'Table 1'[Route1] )
        )
    )

About calculating Round 2 vs Round 1 of Route A, you can create this measure:

Average A = 
VAR tab =
    ADDCOLUMNS (
        'Table 1',
        "R2 vs R1",
            CALCULATE (
                DIVIDE (
                    CALCULATE ( SUM ( 'Table 1'[Rate] ), 'Table 1'[Round] = "Round 2" ),
                    CALCULATE ( SUM ( 'Table 1'[Rate] ), 'Table 1'[Round] = "Round 1" ),
                    0
                ),
                FILTER (
                    'Table 1',
                    'Table 1'[Route1] = EARLIER ( 'Table 1'[Route1] )
                        && 'Table 1'[Route] = EARLIER ( 'Table 1'[Route] )
                )
            )
    )
RETURN
    AVERAGEX ( FILTER ( tab, [Round] = "Round 1" && [R2 vs R1] <> 0 ), [R2 vs R1] )

Attached a sample file in the below, hoeps it could help.

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 · ‎04-05-2021

Hi, @v-yingjl

Thanks for your support!.

However, Round 2 vs Round 1 of Route A should be 1.14, not 0.95. Cuz the Round 2 vs Round 1 of Route A just calculate the average of Round 2 vs Round 1 of Route A that different 0 .

v-yingjl · ‎04-05-2021

Hi @ngocnguyen ,

Oh, I misread the filter condition😄, just modify the filter condition in averagex() like this based on the previous measure:

    AVERAGEX ( FILTER ( tab, [Route] = "A" && [R2 vs R1] <> 0 ), [R2 vs R1] )

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 · ‎04-05-2021

@v-yingjl

Thank you so much for great support!

So, with the same logic as above, If I have many transporter as below table 1. and I wanna create 2 slicers :" Transporter " & "Round".

For example , if I choose slicer " Transporter" : M & "Round" : 3

I can got the 2nd table in the right-hand side ?

v-yingjl · ‎04-05-2021

Hi @ngocnguyen ,

Based on your description, need to modify the previous logic and the acutual value seems not same as your picrture shows after caculating.

The 'Transporter' slicers uses your table field [Transporter], but the 'Round' slicer need to create a new calculated table like this and use it as a slicer:

Round table = DISTINCT('Table'[Round])

Create measures like this:

average measure:

Average =
VAR _transporter =
    SELECTEDVALUE ( 'Table'[Transporter] )
VAR currentround =
    SELECTEDVALUE ( 'Round table'[Round] )
VAR previousround = currentround - 1
VAR tab =
    ADDCOLUMNS (
        'Table',
        "current vs previous",
            CALCULATE (
                DIVIDE (
                    CALCULATE (
                        SUM ( 'Table'[Rate] ),
                        'Table'[Round] = currentround,
                        'Table'[Transporter] = _transporter
                    ),
                    CALCULATE (
                        SUM ( 'Table'[Rate] ),
                        'Table'[Round] = previousround,
                        'Table'[Transporter] = _transporter
                    ),
                    0
                ),
                FILTER (
                    'Table',
                    'Table'[Route1] = EARLIER ( 'Table'[Route1] )
                        && 'Table'[Route] = EARLIER ( 'Table'[Route] )
                )
            )
    )
RETURN
    AVERAGEX ( FILTER ( tab, [current vs previous] <> 0 ), [current vs previous] )

vs measure:

Current round vs Previous round =
VAR _transporter =
    SELECTEDVALUE ( 'Table'[Transporter] )
VAR currentround =
    SELECTEDVALUE ( 'Round table'[Round] )
VAR previousround = currentround - 1
VAR _previous =
    CALCULATE (
        SUM ( 'Table'[Rate] ),
        'Table'[Round] = previousround,
        'Table'[Transporter] = _transporter
    )
VAR _current =
    CALCULATE (
        SUM ( 'Table'[Rate] ),
        'Table'[Round] = currentround,
        'Table'[Transporter] = _transporter
    )
RETURN
    CALCULATE (
        DIVIDE ( _current, _previous, 0 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Route]
                IN DISTINCT ( 'Table'[Route] )
                    && 'Table'[Route1]
                        IN DISTINCT ( 'Table'[Route1] )
                            && 'Table'[Transporter] IN DISTINCT ( 'Table'[Transporter] )
        )
    )

If wants to show it in the table visual, needs to create a visual control like this, set its value as 1 and put it in the table visual filter:

visual control =
IF (
    SELECTEDVALUE ( 'Round table'[Round] ) = SELECTEDVALUE ( 'Table'[Round] )
        || SELECTEDVALUE ( 'Round table'[Round] ) - 1
            = SELECTEDVALUE ( 'Table'[Round] ),
    1,
    0
)

The whole report page:

Attached a sample file in the below, hopes it could help.

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 · ‎04-06-2021

@v-yingjl

Big thanks for your strong support.

Cuz I really wanna to get the result that I explain before. So, How about if I convert the original data to below table in power query, then calculate Round 2 vs Round 1, round 3 vs round 2..... row by row.

If I do like that , what measure should I use to get the bar chart and the table as I mentioned previously?

Syndicate_Admin · ‎04-06-2021

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