Forum Discussion

deb_power123's avatar
deb_power123
Helper V
4 years ago
Solved

Custom table creation with monthly average values

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 under category,Paramscore and current month columns.

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi deb_power123 ,

     

    Please use CROSSJOIN() to create a new table:

     

    NewTable =
var _t1= DISTINCT( SELECTCOLUMNS('Table',"YearMonth",FORMAT([AuditDate],"yyyy mmmm")))
var _t2=SELECTCOLUMNS({"AverageScore","AveragePercentage"},"Category",[Value])
return CROSSJOIN(_t1,_t2)

     

    Then create a column:

     

    ParamScore = SWITCH([Category],"AverageScore",CALCULATE(AVERAGE('Table'[Score]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),"AveragePercentage",CALCULATE(AVERAGE('Table'[PercentageStudents]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])))

     

    Output:

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • deb_power123's avatar
    deb_power123
    4 years ago

    works perfectly 🙂  ...Its a little new concepts for me but I am happy to learn....

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi deb_power123 ,

     

    Please use CROSSJOIN() to create a new table:

     

    NewTable =
var _t1= DISTINCT( SELECTCOLUMNS('Table',"YearMonth",FORMAT([AuditDate],"yyyy mmmm")))
var _t2=SELECTCOLUMNS({"AverageScore","AveragePercentage"},"Category",[Value])
return CROSSJOIN(_t1,_t2)

     

    Then create a column:

     

    ParamScore = SWITCH([Category],"AverageScore",CALCULATE(AVERAGE('Table'[Score]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),"AveragePercentage",CALCULATE(AVERAGE('Table'[PercentageStudents]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])))

     

    Output:

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • deb_power123's avatar
      deb_power123
      Helper V
      4 years ago

      works perfectly 🙂  ...Its a little new concepts for me but I am happy to learn....

    • deb_power123's avatar
      deb_power123
      Helper V
      4 years ago

      Hi Anonymous 

      Is there any way we can modify the format to show the percentage value in your above calculations ?Because in the above calculations the category column with Averagepercentage shows the values in decimal format? Eg: 2021Aug it is .39875 instead of 39.8% format.Is it possible to format?Please suggest

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi deb_power123 ,

     

    To my knowledge, there is only one format available in a single column.

     

    You could format it like:

    =FORMAT([VALUE],"0.00%")

    But the data type would be changed to Text instead.

    Format ParamScore (return Text) = SWITCH([Category],
"AverageScore", CONVERT( CALCULATE(AVERAGE('Table'[Score]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),STRING),
"AveragePercentage",FORMAT( CALCULATE(AVERAGE('Table'[PercentageStudents]),FILTER('Table',FORMAT([AuditDate],"yyyy mmmm")=[YearMonth])),"0.000%"))

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • deb_power123's avatar
      deb_power123
      Helper V
      4 years ago

      Hi Anonymous Its a great solution but the problem will occur when we need to perform any arithematic calculations with the given measure Paramscore because it is in text format so it wont support any addition or substraction from any other measure or column....

       

       

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    deb_power123 you should add a calendar dimension in your model, you can follow my blog post to add  Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

     

    once it is done, use month and year from calendar dimension and use following measure for average to visualize:

     

    Avg Score = AVERAGE ( Table[Score] )

Avg %= AVERAGE ( Table[Percentage] )

    Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

    • deb_power123's avatar
      deb_power123
      Helper V
      4 years ago

      parry2k Its because I have huge database table [many are unrelated so I need to sum them or gather the data together), you can say a data ware house with all tables.I need these data so that I can use them in another homepage of my visualization.

       

      please take a look at the output , you can related what i mean above..