Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-yiruan-msft
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
v-yiruan-msft
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.

Hi @v-yiruan-msft , 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 @v-yiruan-msft , 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 @v-yiruan-msft !! 😀

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.