cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MattGlover Frequent Visitor
Frequent Visitor

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.
 
SS1.png
1 ACCEPTED SOLUTION

Accepted Solutions
v-joesh-msft Established Member
Established Member

Re: Graphing two Quick Measures

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 )

31.PNG

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.

View solution in original post

6 REPLIES 6
v-joesh-msft Established Member
Established Member

Re: Graphing two Quick Measures

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.

1.PNG

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 Frequent Visitor
Frequent Visitor

Re: Graphing two Quick Measures

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 Established Member
Established Member

Re: Graphing two Quick Measures

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:

11.PNG

 

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%.

12.PNG2. 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 Frequent Visitor
Frequent Visitor

Re: Graphing two Quick Measures

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.

 

ss02.png

v-joesh-msft Established Member
Established Member

Re: Graphing two Quick Measures

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 )

31.PNG

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.

View solution in original post

MattGlover Frequent Visitor
Frequent Visitor

Re: Graphing two Quick Measures

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 422 members 3,880 guests
Please welcome our newest community members: