topic Re: DAX query to create DAX table with average values per month against category in DAX Commands and Tips

DAX query to create DAX table with average values per month against category

deb_power123 — Mon, 17 Jan 2022 15:41:26 GMT

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

Sameer

Re: DAX query to create DAX table with average values per month against category

smpa01 — Mon, 17 Jan 2022 15:48:22 GMT

deb_power123 please follow the attached pbix, best practice with Date Table

Re: DAX query to create DAX table with average values per month against category

ValtteriN — Mon, 17 Jan 2022 15:59:15 GMT

Hi,

Smpa01's solution should suffice, but if you insist on calculated table you can use the following DAX:

CalculatedTable = UNION(

SUMMARIZE(AverageX_Score,AverageX_Score[AuditDate].[Month],"Value",AVERAGE(AverageX_Score[Score])),

SUMMARIZE(AverageX_Score,AverageX_Score[AuditDate].[Month],"Value",format(AVERAGE(AverageX_Score[PercentageStudents]),"0.0%"))

)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: DAX query to create DAX table with average values per month against category

deb_power123 — Tue, 18 Jan 2022 04:46:47 GMT

Hi ValtteriN I am unable to replicate your calculation, is it possible for you to share the .pbix file as shared by smpa01?

Also the calculation you performed looks perfect , but is it feasible to add a measure column as below which schould categorize the averagescore and average pecentage? It needs to be placed inside column entries and not as the column header as shown in the above solution of smpa01.

It is fine if we can just place these static values in the table in anyways like we do in data table but not sure if this is supported in calculated table ?

Month	Value	Measures
August	0.1875	Average_score
September	0.75	Average_score
August	39.90%	Average_percentage
September	36.10%	Average_percentage

I truly appreciate for all your help . It should be contained in the columns because I will require to show these values in my visualization and we cant show the value if it is contained in a column header.

Kind regards

Sameer

Re: DAX query to create DAX table with average values per month against category

deb_power123 — Tue, 18 Jan 2022 04:46:05 GMT

Hi smpa01 Thankyou

The calculation looks perfect , but is it feasible to add a measure column as below which schould categorize the averagescore and average pecentage? Both these values are measures since you have used the Average function to calculate them in the .pbix file.

It is fine if we can just place these static values in the table in anyways like we do in data table. Please suggest if this can be achieved in the file below you shared.I am really curious if there is any possible approach since its very close to what I was looking for.

Month	Value	Measures
August	0.1875	Average_score
September	0.75	Average_score
August	39.90%	Average_percentage
September	36.10%	Average_percentage

In your solution , Averagepct and AverageScore are column headers but I want them to appear under column entry as shown above or in any other possible way but it should be contained in the columns because I will require to show these values in my visualization and we cant show the value if it is contained in a column header.

I truly appreciate for all your help .

Kind regards

Sameer

Re: DAX query to create DAX table with average values per month against category

ValtteriN — Tue, 18 Jan 2022 06:42:58 GMT

Hi,
deb_power123

I am unable to share the file but here is a more detailed explanation. Maybe this helps:

Source data:

Go to table tools and select new table:

For the DAX explanation:

The DAX is separated into two different calculated tables which are appended together using UNION. The basic idea is to use SUMMARIZE to group value by month (hence this:

AverageX_Score[AuditDate].[Month]). Then we perfrom the aggregate calculation we want (AVERAGE in this case:

AVERAGE(AverageX_Score[Score]))

Re: DAX query to create DAX table with average values per month against category

wdx223_Daniel — Tue, 18 Jan 2022 07:16:56 GMT

NewTable=GENERATE(

NewTable = GENERATE ( SUMMARIZE ( SELECTCOLUMNS ( 'Table', "CurrentMonth", FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" ) ), [CurrentMonth] ), ADDCOLUMNS ( DATATABLE ( "Category", STRING, { { "AverageScore" }, { "AveragePercentage" } } ), "ParamScore", VAR _m = [CurrentMonth] RETURN IF ( [Category] = "AverageScore", FORMAT ( CALCULATE ( AVERAGE ( 'Table'[Score] ), FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" ) = _m ), "" ), FORMAT ( CALCULATE ( AVERAGE ( 'Table'[PercentageStudents] ), FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" ) = _m ), "0%" ) ) ) )

Re: DAX query to create DAX table with average values per month against category

deb_power123 — Tue, 18 Jan 2022 12:13:20 GMT

Hi wdx223_Daniel This is exactly I was looking for , thankyou for letting me know the approach.I treid to implement is and it worked.Thanks a ton.

Re: DAX query to create DAX table with average values per month against category

deb_power123 — Tue, 25 Jan 2022 12:32:30 GMT

Hi wdx223_Daniel

Here the Score column in input table is in unit of Billion but I want to convert and show the value in Million and put M as a suffix. so 1billion = 1000 million

In ParamScore column, I want to display 0.1875 and 0.75 to 187.5 M and 750 M respectively.

Is it possible and if so please suggest the changes in the above formula?

We require to show the below :-

1. We need to convert from billion to million

2. Add M suffix in the above loop formula

Kind regards

Sameer

Re: DAX query to create DAX table with average values per month against category

wdx223_Daniel — Wed, 26 Jan 2022 02:22:26 GMT

IF ( [Category] = "AverageScore", FORMAT ( CALCULATE ( AVERAGE ( 'Table'[Score] ), FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" ) = _m )*1000, "0.00M" )