Forum Discussion
Scatter Plot with same value column with axis split by attribute
Hi All,
First time poster, long time lurker. A lot of my questions have been answered by other posts, so thanks to the community!
I am working on a dashboard which contains student data.
My source spreadsheet has the following headers
In PowerBi all i have done to the data is unpivoted report year, thinking it might help in my quest.
What I would like to achieve, by using the built in scatter plot, is to display student results, with a selected report year on one axis, and another selected report year on the other.
Eg: I want to show 2024 results on the X and 2023 results on the Y.
An Example of what I have in my table:
Obviously sharing a pbix or more data would be beneficial, however due to the nature of the data, I cannot share this.
Is this something I can achieve with the data I have or do I need to bring in data for each reporting year over in single sources. eg; 2024 report results in one table, 2023 results in another, and compare that way? I can do this, but for trying to keep it tidy (Year 7 to 12, 2 reports per year, it will get messy, quickly), I have saved all data in one sheet.
Thank you in advance
Anonymous , Create two measures, for the selected year /vs last year or this year vs last year. In both cases prefer a separate date/year table.
Measure based on the selected value
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Year'),'Year'[Year]=max('Year'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Year'),'Year'[Year]=max('Year'[Year])-1))based on today
This year Today =
var _min = eomonth(today(),-1*month(today()))+1
var _max = eomonth(_min,12 -1*month(today()) )
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))Last year Today =
var _max = eomonth(today(),-1*month(today()))
var _min = eomonth(_max,-12)+1
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))Hi Anonymous ,
I don't think you can use the same column from the same table in both the axis of a scatter chart. This is because when you add the same column to both the axis it creates a one-to-one mapping between the values in both the axis.
Moreover, the scattercharts are used to analyze the relation between two numerical values. So having a year (datatype as number) in the axis would most likely result in a one-to-one mapping.
Alternatively, you can create a new column in the same table that would have the year value that want to add to the Y-axis. But I doubt the scalability of this technique. I tried for 2 years, from 2022-2024, I was working. You can give it a try
Regards,
2 Replies
Anonymous , Create two measures, for the selected year /vs last year or this year vs last year. In both cases prefer a separate date/year table.
Measure based on the selected value
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Year'),'Year'[Year]=max('Year'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Year'),'Year'[Year]=max('Year'[Year])-1))based on today
This year Today =
var _min = eomonth(today(),-1*month(today()))+1
var _max = eomonth(_min,12 -1*month(today()) )
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))Last year Today =
var _max = eomonth(today(),-1*month(today()))
var _min = eomonth(_max,-12)+1
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))Hi Anonymous ,
I don't think you can use the same column from the same table in both the axis of a scatter chart. This is because when you add the same column to both the axis it creates a one-to-one mapping between the values in both the axis.
Moreover, the scattercharts are used to analyze the relation between two numerical values. So having a year (datatype as number) in the axis would most likely result in a one-to-one mapping.
Alternatively, you can create a new column in the same table that would have the year value that want to add to the Y-axis. But I doubt the scalability of this technique. I tried for 2 years, from 2022-2024, I was working. You can give it a try
Regards,