Forum Discussion

Frequent Visitor

4 years ago

Solved

How to average %YOY and plot a graph?

Hi Power BI Community,

I need to average the AVE_%YOY to get the same dynamic results as in Figure. The figure it`s shown how the percentage must be calculated correctly for this case.
Resume: I need it to calculate for SE and similar like CO and Other regions. In this example, by coincidence, as CO only has one Country linked in the example, your AVERAGE = P1. How to Return CO, SE, "n" the result of an AVERAGE(SUM(X1:n)) measure?

The base has more than 40 countries in 5 regions. Here's a small example of the base to illustrate the problem in csv.

Table_Index

 Country;DscRegion;Data;Value
 P1;CO;01/jan/08;490
 P1;CO;01/jan/09;609
 P1;CO;01/jan/10;728
 P1;CO;01/jan/11;788
 P2;SE;01/jan/08;241
 P2;SE;01/jan/09;158
 P2;SE;01/jan/10;286
 P2;SE;01/jan/11;322
 P3;SE;01/jan/08;97
 P3;SE;01/jan/09;109
 P3;SE;01/jan/10;151
 P3;SE;01/jan/11;65
Table_calendar

DataBASE
terça-feira, 1 de janeiro de 2008
quinta-feira, 1 de janeiro de 2009
sexta-feira, 1 de janeiro de 2010
sábado, 1 de janeiro de 2011

Step by step developed:
1- Create the measure [Total_X3]

  [Values] = SUM(X3[Value])
2 - Create the YOY measure

 [Value -1 YEAR]=CALCULATE(SUM(X3[Value]),SAMEPERIODLASTYEAR(CALENDARIO[DataBASE]))
3 - Create the measure [output_AVE_%YOY]

 Avg_%YOY = AVERAGEX(VALUES(CALENDARIO[ANO]),[SUM_DIV_YOY])
    
 -- [Values] = SUM(X3[Value])
 -- [Value -1 YEAR]=CALCULATE(SUM(X3[Value]),SAMEPERIODLASTYEAR(CALENDARIO[DataBASE]))
 -- [DIV_YOY] = DIVIDE([Values]-[Value -1 YEAR],[Value -1 YEAR])
 -- [SUM_DIV_YOY] = SUMX(VALUES(CALENDARIO[ANO]),[DIV_YOY])

Notes:

[Value] measures are correct.
The Column average calculation [AVE_%YOY ][output] in Figure 1 is "wrong".
The column [AVE_%YOY][output] in Figure 1 shows the calculation performed by the PWBI.
Were manually filled in to illustrate the expected results from [AVE_%YOY][expected output] in Figura 1.
The [AVE_%YOY][expected output] in Figura 1 measure must also work on a Card.
The objective is to calculate the measure [AVE_%YOY ][output] in Figure 1 so that it returns dynamic values equal to the column [AVE_%YOY][expected output] in Figura 1.

Figure 1 is the expected_output is correct:

The problem lies in the calculations highlighted in yellow in figure 1.

For example:

How does PWBI manage to do this wrong account?
Average(SUM(19.72;-2.02)) or AVERAGE(SUM(P1,P2)) = 10.41. It,s is ignoring the signs? The correct would not be 8.85? The results highlighted in yellow In Excel and R, both return the corresponding values from column [AVE_%YOY][expected output],I need to get the same result dynamically in PWBI to attach to my PhD work.

If possible I would like to be able to generate a dynamic graph similar to this example:

Regards
Cleber

Hi,

Please check the below picture and the attached pbix file.

Value measure: = 
SUM( Data[Value] )

percent YoY: = 
VAR _currentyear = [Value measure:]
VAR _previousyear =
    CALCULATE ( [Value measure:], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
VAR _yoypercent =
    IF (
        _currentyear <> BLANK ()
            && _previousyear <> BLANK (),
        _currentyear / _previousyear - 1
    )
RETURN
    _yoypercent

expected result: = 
VAR _currentcountry =
    MAX ( Country[Country] )
VAR _currentregion =
    MAX ( DscRegion[DscRegion] )
VAR _countrytable =
    FILTER (
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE ( ALL ( Data ), Country[Country], 'Calendar'[Year CC] ),
                Country[Country] = _currentcountry
            ),
            "@yoypercent", [percent YoY:]
        ),
        [@yoypercent] <> BLANK ()
    )
VAR _regiontable =
    FILTER (
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE (
                    ALL ( Data ),
                    DscRegion[DscRegion],
                    Country[Country],
                    'Calendar'[Year CC]
                ),
                DscRegion[DscRegion] = _currentregion
            ),
            "@yoypercent", [percent YoY:]
        ),
        [@yoypercent] <> BLANK ()
    )
VAR _notblanktable =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Data, DscRegion[DscRegion], Country[Country], 'Calendar'[Year CC] ),
            "@yoypercent", [percent YoY:]
        ),
        [@yoypercent] <> BLANK ()
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Calendar'[Year CC] ), [percent YoY:],
        ISINSCOPE ( Country[Country] ), AVERAGEX ( _countrytable, [@yoypercent] ),
        ISINSCOPE ( DscRegion[DscRegion] ), AVERAGEX ( _regiontable, [@yoypercent] ),
        AVERAGEX ( _notblanktable, [@yoypercent] )
    )

cnauber.pbix41 KB

Jihwan_Kim

4 years ago

Hi,

Thank you for your message, and please check the below picture and the attached pbix file.

AVG National: = 
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE ( Data, Country[Country], 'Calendar'[Year CC] ),
        "@percent", [percent YoY:]
    ),
    [@percent]
)

AVG P2: = 
CALCULATE( [expected result:],Country[Country] = "P2" )

AVG CO: = 
AVERAGEX (
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE ( ALL ( Data ), DscRegion[DscRegion], Country[Country] ),
            DscRegion[DscRegion] = "CO"
        ),
        "@percent", [percent YoY:]
    ),
    [@percent]
)

AVG SE: = 
AVERAGEX (
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE ( ALL ( Data ), DscRegion[DscRegion], Country[Country] ),
            DscRegion[DscRegion] = "SE"
        ),
        "@percent", [percent YoY:]
    ),
    [@percent]
)

cnauber.pbix42 KB

4 Replies

cnauber
Frequent Visitor
4 years ago
Hi, Jihwan_Kim.
Thank you very much for your help and availability. His help was clear and to the point.

Jihwan_Kim

Super User

4 years ago

Hi,

Please check the below picture and the attached pbix file.

Value measure: = 
SUM( Data[Value] )

percent YoY: = 
VAR _currentyear = [Value measure:]
VAR _previousyear =
    CALCULATE ( [Value measure:], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
VAR _yoypercent =
    IF (
        _currentyear <> BLANK ()
            && _previousyear <> BLANK (),
        _currentyear / _previousyear - 1
    )
RETURN
    _yoypercent

expected result: = 
VAR _currentcountry =
    MAX ( Country[Country] )
VAR _currentregion =
    MAX ( DscRegion[DscRegion] )
VAR _countrytable =
    FILTER (
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE ( ALL ( Data ), Country[Country], 'Calendar'[Year CC] ),
                Country[Country] = _currentcountry
            ),
            "@yoypercent", [percent YoY:]
        ),
        [@yoypercent] <> BLANK ()
    )
VAR _regiontable =
    FILTER (
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE (
                    ALL ( Data ),
                    DscRegion[DscRegion],
                    Country[Country],
                    'Calendar'[Year CC]
                ),
                DscRegion[DscRegion] = _currentregion
            ),
            "@yoypercent", [percent YoY:]
        ),
        [@yoypercent] <> BLANK ()
    )
VAR _notblanktable =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Data, DscRegion[DscRegion], Country[Country], 'Calendar'[Year CC] ),
            "@yoypercent", [percent YoY:]
        ),
        [@yoypercent] <> BLANK ()
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Calendar'[Year CC] ), [percent YoY:],
        ISINSCOPE ( Country[Country] ), AVERAGEX ( _countrytable, [@yoypercent] ),
        ISINSCOPE ( DscRegion[DscRegion] ), AVERAGEX ( _regiontable, [@yoypercent] ),
        AVERAGEX ( _notblanktable, [@yoypercent] )
    )

cnauber.pbix41 KB

cnauber

Frequent Visitor

4 years ago

Dear, Jihwan_Kim . Yes, the calculation part is perfect. Is the part of the dynamic inline graph similar to the above example possible to be added to the attached pbix file, or is it necessary to open another call? Thanks very much for the help.

Jihwan_Kim

Super User

4 years ago

Hi,

Thank you for your message, and please check the below picture and the attached pbix file.

AVG National: = 
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE ( Data, Country[Country], 'Calendar'[Year CC] ),
        "@percent", [percent YoY:]
    ),
    [@percent]
)

AVG P2: = 
CALCULATE( [expected result:],Country[Country] = "P2" )

AVG CO: = 
AVERAGEX (
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE ( ALL ( Data ), DscRegion[DscRegion], Country[Country] ),
            DscRegion[DscRegion] = "CO"
        ),
        "@percent", [percent YoY:]
    ),
    [@percent]
)

AVG SE: = 
AVERAGEX (
    ADDCOLUMNS (
        FILTER (
            SUMMARIZE ( ALL ( Data ), DscRegion[DscRegion], Country[Country] ),
            DscRegion[DscRegion] = "SE"
        ),
        "@percent", [percent YoY:]
    ),
    [@percent]
)

cnauber.pbix42 KB

Forum Discussion

How to average %YOY and plot a graph?

4 Replies

Related content

Average and Moving Average

Fabric graph

Calculate YoY change

YOY%

YoY on a graph