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

Column/linechart variance

Hey

 

Setup:

Compare revenue of several products. 

Column: Revenue, per year, multiple products

Line: Variance Y-t-Y

 

Now the problem is that I only get 1 line. If multiple objects selected, it just takes the total variance and not the individual variance per object. 

For example. 3 products, 3 years.

For each year, 3 columns, one per product. 3 lines throughout the chart for the variance. 

 

 

Thanks a lot! 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Soler_1 

In this case, you need to create individual measures for different products. For example:

Variance % A = 
VAR thisYear = SELECTEDVALUE('Table'[Year])
VAR thisYearRevenue = CALCULATE(SUM('Table'[Revenue]),ALLEXCEPT('Table','Table'[Year]),'Table'[Product]="A")
VAR lastYearRevenue = CALCULATE(SUM('Table'[Revenue]),ALL('Table'),'Table'[Year]=thisYear-1,'Table'[Product]="A")
RETURN
DIVIDE(thisYearRevenue-lastYearRevenue,lastYearRevenue)

Change the product name in the measure to create multiple measures for multiple lines in the chart. Here is a pbix file for reference.

021001.jpg

 

Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @Soler_1 

In this case, you need to create individual measures for different products. For example:

Variance % A = 
VAR thisYear = SELECTEDVALUE('Table'[Year])
VAR thisYearRevenue = CALCULATE(SUM('Table'[Revenue]),ALLEXCEPT('Table','Table'[Year]),'Table'[Product]="A")
VAR lastYearRevenue = CALCULATE(SUM('Table'[Revenue]),ALL('Table'),'Table'[Year]=thisYear-1,'Table'[Product]="A")
RETURN
DIVIDE(thisYearRevenue-lastYearRevenue,lastYearRevenue)

Change the product name in the measure to create multiple measures for multiple lines in the chart. Here is a pbix file for reference.

021001.jpg

 

Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

View solution in original post

Thanks ! 

parry2k
Super User III
Super User III

@Soler_1 Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors