cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI_newuser
Post Prodigy
Post Prodigy

Growth movement of scatter plot

Hi, is it possible to plot the below chart in power bi?

I want to compare the result for last year and this year in the same chart.

 

PBI_newuser_0-1625544818059.png

 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @PBI_newuser ,

I created a sample pbix file(see attachment), please check whether that is what you want.

yingyinr_0-1625808882915.png

Best Regards

Community Support Team _ Rena
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

9 REPLIES 9
yingyinr
Community Support
Community Support

Hi @PBI_newuser ,

I created a sample pbix file(see attachment), please check whether that is what you want.

yingyinr_0-1625808882915.png

Best Regards

Community Support Team _ Rena
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

Hi @yingyinr , I have a question.

My data's fiscal year starts on October 01 and ends on September 30.

For example, current month is July. I would like to calculate all the measures from October to June only.

In FY2019 (Oct'18 to Sep'19) and FY2020 (Oct'19 to Sep'20), I have 12 months data but for FY2021 (Oct'19 to Jun'21), I have only 9 months data.

In the scatterplot, the Sales % and Revenue should calculate from Oct to Jun only for each fiscal year. 
FY2019 = Oct'18 to Jun'19

FY2020 = Oct'19 to Jun'20

FY2021 = Oct'20 to Jun'21

May I know how calculate the measures up to last month for all the fiscal year? Thank you.

Hi @PBI_newuser ,

You can create measure as below(Assume that the table exist [Fisal Year] and [month number] field):

Measure =
VAR _maxdatemonth =
    MONTH ( MAX ( 'Table'[Date] ) )
VAR _revenue =
    CALCULATE (
        SUM ( 'Table'[Revenue] ),
        FILTER (
            'Table',
            'Table'[Fiscal Year] = SELECTEDVALUE ( 'Table'[Fiscal Year] )
                && IF (
                    _maxdatemonth >= 10,
                    'Table'[Month] >= 10
                        && 'Table'[Month] <= _maxdatemonth,
                    ( 'Table'[Month]
                        IN { 10, 11, 12 }
                        || 'Table'[Month] >= 1
                        && 'Table'[Month] <= _maxdatemonth )
                )
        )
    )
RETURN
    _revenue

Best Regards

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

Hi @yingyinr , the measure doesn't work. Here is the sample.

In the sample data, the max month is 2021-05.

I would like to view the result for each fiscal year from Oct to May. How to do it? Thanks.

PBI_newuser_0-1626228580379.png

 

Hi @PBI_newuser ,

I update the formula of measure as below and it can get your expected output just as shown in below screenshot. Please find the details in the attachment.

Measure =
VAR _maxdatemonth =
MONTH( CALCULATE( MAX ( 'Table'[Month] ),ALL('Table')) )
VAR _revenue =
CALCULATE (
[Ave Revenue/Order],
FILTER (
'Calendar',
'Calendar'[Fiscal Year] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
&& IF (
_maxdatemonth >= 10,
'Calendar'[Month number] >= 10
&& 'Calendar'[Month number] <= _maxdatemonth,
( 'Calendar'[Month number]
IN { 10, 11, 12 }
|| 'Calendar'[Month number] >= 1
&& 'Calendar'[Month number] <= _maxdatemonth )
)
)
)
RETURN
_revenue

yingyinr_0-1626241646299.png

Best Regards

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

Thanks @yingyinr !! 😀

RJ251997
Helper I
Helper I

HI @PBI_newuser 

You can follow this link its very helpful for you 

https://radacad.com/storytelling-with-power-bi-scatter-chart

 

Thank & Reagrds

Rohit Jaiswal

Hi @RJ251997 , is it possible for the bubbles to be static for each year?

@PBI_newuser , Tried something like this.

Product and Year on Details. ( Drill till last level using reverse Y sign)

AUSP and Sales % on axis.

 

And conditional formating using scale on YOY measure



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors