Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
deb_power123
Helper V
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.

 

AuditDateScoreSchoolNamePercentageStudents
15.08.2021-2A20%
15.08.2021-1B30%
16.08.20213C22%
16.08.20210D45%
16.08.2021-1A65%
17.08.2021-1B17%
17.08.2021-1C29%
18.08.20213A78%
18.08.20210C87%
19.08.2021-1C22%
19.08.20213D45%
19.08.20210E65%
20.08.2021-1B17%
21.08.20212D29%
22.08.20211E22%
22.08.2021-1A45%
1.09.20210E65%
1.09.2021-1B17%
2.09.20212D29%
3.09.20211E22%
3.09.2021-1A45%
4.09.20211C65%
5.09.20211C17%
5.09.20213A29%

 

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.

 

CategoryParamScoreCurrentMonth
AverageScore0.1875August
AverageScore0.75September
AveragePercentage40%August
AveragePercentage36%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
wdx223_Daniel
Super User
Super User

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

wdx223_Daniel_0-1642490205868.png

 

View solution in original post

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.

View solution in original post

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

View solution in original post

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

View solution in original post

9 REPLIES 9
wdx223_Daniel
Super User
Super User

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

wdx223_Daniel_0-1642490205868.png

 

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

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

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
Super User
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%"))
)
 
 
ValtteriN_0-1642435138275.png

 


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!




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 ?

:-

 

MonthValue     Measures
August0.1875Average_score
September0.75Average_score
August39.90%Average_percentage
September36.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

 

 

Hi,
@deb_power123 

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

Source data:

ValtteriN_0-1642487868056.png



Go to table tools and select new table:

ValtteriN_1-1642488016894.png

 

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!




smpa01
Super User
Super User

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

smpa01_0-1642434465894.png

 

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

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.

 

MonthValue     Measures
August0.1875Average_score
September0.75Average_score
August39.90%Average_percentage
September36.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

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.