Custom table creation with monthly average values

[deb_power123]

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 under category,Paramscore and current month columns.

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

Sameer

[v-eqin-msft · Accepted solution]

Hi @deb_power123 ,

Please use CROSSJOIN() to create a new table:

NewTable =
var _t1= DISTINCT( SELECTCOLUMNS('Table',"YearMonth",FORMAT([AuditDate],"yyyy mmmm")))
var _t2=SELECTCOLUMNS({"AverageScore","AveragePercentage"},"Category",[Value])
return CROSSJOIN(_t1,_t2)

Then create a column:

ParamScore = SWITCH([Category],"AverageScore",CALCULATE(AVERAGE('Table'[Score]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),"AveragePercentage",CALCULATE(AVERAGE('Table'[PercentageStudents]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])))

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-eqin-msft]

Hi @deb_power123 ,

To my knowledge, there is only one format available in a single column.

You could format it like:

=FORMAT([VALUE],"0.00%")

But the data type would be changed to Text instead.

Format ParamScore (return Text) = SWITCH([Category],
"AverageScore", CONVERT( CALCULATE(AVERAGE('Table'[Score]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),STRING),
"AveragePercentage",FORMAT( CALCULATE(AVERAGE('Table'[PercentageStudents]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),"0.000%"))

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[deb_power123]

Hi @v-eqin-msft Its a great solution but the problem will occur when we need to perform any arithematic calculations with the given measure Paramscore because it is in text format so it wont support any addition or substraction from any other measure or column....

[v-eqin-msft · Accepted solution]

Hi @deb_power123 ,

Please use CROSSJOIN() to create a new table:

NewTable =
var _t1= DISTINCT( SELECTCOLUMNS('Table',"YearMonth",FORMAT([AuditDate],"yyyy mmmm")))
var _t2=SELECTCOLUMNS({"AverageScore","AveragePercentage"},"Category",[Value])
return CROSSJOIN(_t1,_t2)

Then create a column:

ParamScore = SWITCH([Category],"AverageScore",CALCULATE(AVERAGE('Table'[Score]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),"AveragePercentage",CALCULATE(AVERAGE('Table'[PercentageStudents]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])))

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[deb_power123]

Hi @v-eqin-msft 

Is there any way we can modify the format to show the percentage value in your above calculations ?Because in the above calculations the category column with Averagepercentage shows the values in decimal format? Eg: 2021Aug it is .39875 instead of 39.8% format.Is it possible to format?Please suggest

[deb_power123 · Accepted solution]

works perfectly 🙂  ...Its a little new concepts for me but I am happy to learn....

[parry2k]

@deb_power123 but why? Why you need a table?

[deb_power123]

@parry2k Its because I have huge database table [many are unrelated so I need to sum them or gather the data together), you can say a data ware house with all tables.I need these data so that I can use them in another homepage of my visualization.

please take a look at the output , you can related what i mean above..

[parry2k]

@deb_power123 you should add a calendar dimension in your model, you can follow my blog post to add  Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

once it is done, use month and year from calendar dimension and use following measure for average to visualize:

Avg Score = AVERAGE ( Table[Score] )

Avg %= AVERAGE ( Table[Percentage] )

Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[deb_power123]

Hi @parry2k 

I actually need it in the tabular view like we have in PowerBI create new table, create measure, create column.I need it in that format.The one you gave above is for measures but I need to combine them all in a table format.

Please see the expected output, it should be incuded in that way..so we need three columns category,score and currentmonth

Kind regards

Sameer

[smpa01]

@deb_power123  answered here

https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-query-to-create-DAX-table-with-average-va...