Scatter Plot with same value column with axis split by attribute

[cassj]

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

[amitchandak · Accepted solution]

@cassj , 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))

[Thejeswar · Accepted solution]

Hi @cassj ,

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,

[amitchandak · Accepted solution]

@cassj , 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))