Forum Discussion

MattGlover's avatar
MattGlover
New Member
6 years ago
Solved

Graphing two Quick Measures

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.
 
Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    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.

6 Replies

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    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/ERIHj58VTCVElwsMKYDDBgUBjHI_HFZJiSC6YhQWL5oDOw?e=TzGv9E

     

    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.

      • v-joesh-msft's avatar
        v-joesh-msft
        Solution Sage
        6 years ago

        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/EcfWOHDv6pBIoigfENPRiUYBsYG257hyP4NE9Zsm0cCJcQ?e=rhuZj3

        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.