transformation
1 Topic
DAX query to create DAX table with average values per month against category
Hi All, I have the below input source table with Audit Date,Score,SchoolName and PercentageStudents columns and table name as Table. I need to find the average of score and percentageStudents per month for 2021 and categorize them in a table. In the below source input table , we have input data for the months of August and September 2021. In real time we will have data for all months in 2021. AuditDate Score SchoolName PercentageStudents 15.08.2021 -2 A 20% 15.08.2021 -1 B 30% 16.08.2021 3 C 22% 16.08.2021 0 D 45% 16.08.2021 -1 A 65% 17.08.2021 -1 B 17% 17.08.2021 -1 C 29% 18.08.2021 3 A 78% 18.08.2021 0 C 87% 19.08.2021 -1 C 22% 19.08.2021 3 D 45% 19.08.2021 0 E 65% 20.08.2021 -1 B 17% 21.08.2021 2 D 29% 22.08.2021 1 E 22% 22.08.2021 -1 A 45% 1.09.2021 0 E 65% 1.09.2021 -1 B 17% 2.09.2021 2 D 29% 3.09.2021 1 E 22% 3.09.2021 -1 A 45% 4.09.2021 1 C 65% 5.09.2021 1 C 17% 5.09.2021 3 A 29% Expected Output I want to create a custom table inside PowerBI which should use the input source table values as above to calculate the average per month and should look as below :- Here : Category has two static values namely "Average Score" and " Average Percentage" in the Category column which should populate against each month average as per below tale matrix. In this example we have taken two months namely August and September but in real scenario it can be all the 12 months in a year. Category ParamScore CurrentMonth AverageScore 0.1875 August AverageScore 0.75 September AveragePercentage 40% August AveragePercentage 36% September Could anyone please help with any possible DAX query to create this output table? Appreciate for all the help in this regard Kind regards SameerSolved6.6KViews0likes9Comments