Graphing two Quick Measures

[MattGlover]

I have a "Line and clustered column chart" visualization in which I am graphing two Quick Measures.  Code for the two measures are:

Total Revenues per Capita = DIVIDE(SUM('Master (2dot0)'[Total Revenues]), SUM('Master (2dot0)'[Population]))

(1.3) Total Revenues per Capita YoY% =

IF(

    ISFILTERED('Master (2dot0)'[LongYear]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR =

        CALCULATE(

            'Master (2dot0)'[Total Revenues per Capita],

            DATEADD('Master (2dot0)'[LongYear].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE(

            'Master (2dot0)'[Total Revenues per Capita] - __PREV_YEAR,

            __PREV_YEAR

        )

)

Everything looks fine and the math check out in the "Table" visualization below, but when I graph the two measures on a bar and line chart, there is a different value for the YoY% measure.  It's listed as 1.11% in the table, but 0.56% on the graph.  Anyone know what's going on?  Thanks.

[v-joesh-msft]

Hi @MattGlover ,

You can check if the context in the matrix is consistent with the context in the graph.

I created a demo below and everything shows normal.

If not your case, please share your sample pbix file for us if you don't have any Confidential Information.

Here is the demo, please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERIHj58VTCVElwsMKY...

Best Regards,

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

[MattGlover]

Hello, thank you for the reply.

I'm unsure what you mean by context of the matrix?  The table shows the correct value:  1.11%.  The graph however shows 0.56% and I do not know why.

I don't see an option to upload the .pbix file.  Are you able to access the following link?

https://www.dropbox.com/s/ujuvmifb2a3wdgg/Sample.pbix?dl=0

[v-joesh-msft]

Hi @MattGlover ,

The easiest way to solve your problem is to deselect Jurisdiction in the Shared axis(if this is not a necessary axis) and replace the field "Year" in the Column series with "Year" in Long Year, as shown in the example below:

The following is a brief description of the cause of the error:

1. Why is it showing 0.56%?

Change the table diagram in the sample file to a matrix diagram, and you will find the difference.

The displayed 0.56% is actually the value in Total, because Total Revenues per Capita = DIVIDE(SUM('Master (2dot0)'[Total Revenues]) , SUM('Master (2dot0)'[Population])), so the total of Total Revenues per Capita in the figure = (2017[Total Revenues]+2018[Total Revenues])/(2017[Population]+2018[Population]) The final calculation is 5047.67. This value does not really make sense. The corresponding (1.3) Total Revenues per Capita YoY%=(5047.67-5019.57)/5019.57, so it will show 0.56%.

2. Why change "Year" to "Long Year"

Variables in Total Revenues per Capita YoY%

__PREV_YEAR =
CALCULATE (
    'Master (2dot0)'[Total Revenues per Capita],
    DATEADD ( 'Master (2dot0)'[LongYear].[Date], -1, YEAR )
)

The "Long Year" field is used, so the fields in the Column series should be consistent.

Here is a demo, please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EcfWOHDv6pBIoigfEN...

Best Regards,

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

[MattGlover]

I appreciate the reply.  I looked at the two changes you mentioned and can replicate the results.  However, 'Jurisdiction' is a required field as a Shared Axis.

Is there a way to accomplish this?  I tried creating an additional measure that simply multiplies the YoY% value by two to counter the NULL values for 2017, but it's not accurate enough.

[v-joesh-msft · Accepted solution]

Hi @MattGlover ,

If you just want the data in the line graph to display correctly, you can try creating the following measure instead (1.3) Total Revenues per Capita YoY%:

mearsure =
IF (
    ISFILTERED ( 'Master (2dot0)'[LongYear] ),
    ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
    VAR __PREV_YEAR =
        CALCULATE (
            'Master (2dot0)'[Total Revenues per Capita],
            FILTER ( 'Master (2dot0)', 'Master (2dot0)'[LongYear].[Year] = 2017 )
        )
    VAR __THIS_YEAR =
        CALCULATE (
            'Master (2dot0)'[Total Revenues per Capita],
            FILTER ( 'Master (2dot0)', 'Master (2dot0)'[LongYear].[Year] = 2018 )
        )
    RETURN
        DIVIDE ( __THIS_YEAR - __PREV_YEAR, __PREV_YEAR )

Best Regards,

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

[MattGlover]

Thank you so much!  I appreciate all of the help.