March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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%"
)
)
)
)
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.
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"
)
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%"
)
)
)
)
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.
Hi,
Smpa01's solution should suffice, but if you insist on calculated table you can use the following DAX:
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!
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 ?
:-
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
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:
Proud to be a Super User!
@deb_power123 please follow the attached pbix, best practice with Date Table
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
22 | |
20 | |
18 |