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

[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.

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

[wdx223_Daniel · Accepted solution]

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%"
                    )
                )
    )
)

[deb_power123 · Accepted solution]

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.

[deb_power123 · Accepted solution]

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

[wdx223_Daniel · Accepted solution]

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%"
                    )
                )
    )
)

[deb_power123 · Accepted solution]

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

[wdx223_Daniel · Accepted solution]

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

[deb_power123 · Accepted solution]

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.

[ValtteriN]

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!

[deb_power123]

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

[ValtteriN]

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

[smpa01]

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

[deb_power123]

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