Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Dynamic Scatter plot

‎06-27-2020 08:49 PM

Hello,

I have a table as below. I would like to build two scatter plot. 

First scatter plot:

  • X-axis - Revenue Budget
  • Y- axis- Revenue Actual
  • Size - Revenue Actual (bigger for larger client)
  • Color - ServiceLine

Second scatter plot:

  • Same as above, but x-axis as revenue from last year.

So, I built a scatter plot for the first view as:

Actual Revenue = if('values'[Revenue]="Revenue",'values'[Actual Value],0)
Budget Revenue = If('values'[Revenue]="Revenue",'values'[Budget Value],0)
Revenue Value = if ('values'[GDR Total]="Revenue",'values'[[Values]]],0)
 
Scatter Plot 1.PNG
 
Now the problem is for the second scatter plot if I follow above method then I would I have to create a column for each specific year example Actual Revenue 2020, Budget Revenue 2020, Actual revenue 2019, Actual revenue 2018.... (The below table is just a sample, I have data going back few years)
Question: I would like to know if there is an easier way to make the scatter plot more dynamic. I would like to have year as a filter and after selecting the year the data in both the scatter plot would change accordingly?
AccountClientService LineOps GeoYearPeriodScenarioData SourceCurrencyMeasureManagerRVP[Values]
1000100FinanceOshkosh20201BudgetInputUSDValueABSDGS400
1000200AccoutingRegina20202BudgetInputUSDValueABSDGS400
1000100ITOshkosh20203BudgetInputUSDValueABSDGS400
2000200FinanceRegina20204BudgetInputUSDValueABSDGS200
2000100AccoutingOshkosh20205BudgetInputUSDValueABSDGS100
2000200ITRegina20206BudgetInputUSDValueABSDGS200
3000100FinanceOshkosh20207BudgetInputUSDValueABSDGS100
3000200AccoutingRegina20208BudgetInputUSDValueABSDGS200
3000100ITOshkosh20209BudgetInputUSDValueABSDGS100
1000200MarketingRegina202010BudgetInputUSDValueABSDGS200
1000100FinanceOshkosh20201ActualInputUSDValueABSDGS500
1000200AccoutingRegina20202ActualInputUSDValueABSDGS500
1000100ITOshkosh20203ActualInputUSDValueABSDGS300
2000200FinanceRegina20204ActualInputUSDValueABSDGS200
2000100AccoutingOshkosh20205ActualInputUSDValueABSDGS200
2000200ITRegina20206ActualInputUSDValueABSDGS100
3000100FinanceOshkosh20207ActualInputUSDValueABSDGS200
3000200AccoutingRegina20208ActualInputUSDValueABSDGS100
3000100ITOshkosh20209ActualInputUSDValueABSDGS200
1000200MarketingRegina202010ActualInputUSDValueABSDGS100
1000100FinanceOshkosh20191BudgetInputUSDValueABSDGS400
1000200AccoutingRegina20192BudgetInputUSDValueABSDGS400
1000100ITOshkosh20193BudgetInputUSDValueABSDGS400
1000200MarketingRegina201910BudgetInputUSDValueABSDGS200
1000100FinanceOshkosh20191ActualInputUSDValueABSDGS500
1000200AccoutingRegina20192ActualInputUSDValueABSDGS500
1000100ITOshkosh20193ActualInputUSDValueABSDGS300
1000200MarketingRegina201910ActualInputUSDValueABSDGS100
             

 

 
Thanks
 
 
Labels:
Message 1 of 4
975 Views
0
3 REPLIES 3
v-yingjl
Community Support
Community Support

‎06-29-2020 01:08 AM

Hi @Anonymous ,

Seems like your sample data table is not matched with your measure field and I have no access to your shared link.

Could you please reshare it for further discussion?

 

Best Regards,

Yingjie Li

Message 4 of 4
896 Views
0
amitchandak
Super User
Super User

‎06-27-2020 08:59 PM

@Anonymous ,

You have to create last year measure

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 2 of 4
971 Views
0
Anonymous
Not applicable
In response to amitchandak

‎06-28-2020 08:34 PM

Hello @amitchandak ,

Thank you for the reply. I created the Date table but as I did not have the actual date in my raw data, I connected the Date column with Period and Year from the raw data. The relationship shows 1: many connections but it seems that the connection is not working.

Relationship 1.PNG

 

The reason I say the connection is not working is because:

 

The table below has year from raw data:

Table 1.PNG

 

were as the below table has year from Date table I  created. It does not bring in the values for a date.

Table 2.PNG

 

How do I fix this?

 

Also, here is the link to the draft dashboard.

https://drive.google.com/drive/u/1/folders/1GUEFuhU10AFsLFYtBmYB97C5QJ_VqV8-

 

 

Message 3 of 4
912 Views
0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All