Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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))
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,
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,
@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))
User | Count |
---|---|
59 | |
59 | |
56 | |
38 | |
29 |
User | Count |
---|---|
78 | |
62 | |
45 | |
40 | |
39 |