cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## 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

Sameer

4 ACCEPTED SOLUTIONS
Super User

NewTable=GENERATE(

``````NewTable =
GENERATE (
SUMMARIZE (
SELECTCOLUMNS (
'Table',
"CurrentMonth", FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" )
),
[CurrentMonth]
),
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%"
)
)
)
)``````

Helper V

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.

Helper V

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

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

NewTable=GENERATE(

``````NewTable =
GENERATE (
SUMMARIZE (
SELECTCOLUMNS (
'Table',
"CurrentMonth", FORMAT ( 'Table'[AuditDate], "MMMM - yyyy" )
),
[CurrentMonth]
),
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%"
)
)
)
)``````

Helper V

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

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

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.

Super User

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!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper V

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

Super User

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]))

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Helper V

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