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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
NKotak_Leecare
Helper III
Helper III

Dynamically calculate Average

Activities_Q&A_Community.pngI need to create a table where we can dynamically group by year and month with average on one axis and total on the other. I have been able to create one but it is static and if I try to make it dynamic Average doesn't come. Static Mock up is attached below:

1 ACCEPTED SOLUTION

Thank you for your reply. I have actually edited the query and transposed the data in a way I get month-year and all the activites from a single query. I am no longer using the Date Query. Instead, I have used group_by to group it based on the activity dates and get the sum for each activities. Next step, I put conditional columns to get the number of sessions occured in a month-year by checking the sum column of that particular activity - i.e. if the sum is > 0 the output in conditional column in 1. Hence I get the number of sessions occured in a month - year. After that, I transposed the query and got columns as month-year and all activities in the row. Then I calculated Average using this Power Query - each List.Average(Record.FieldValues(_ & [Session Type = null])). It did work perfect! 

View solution in original post

14 REPLIES 14
v-chenwuz-msft
Community Support
Community Support

Hi @NKotak_Leecare ,

 

I recommend matrix table and Date Hierarchy to reach that.

First, you need a calendar table or date column, this will be used for date hierarchy.

Second, create a measure like the following:

Measure =
IF(
    HASONEVALUE( 'Table'[Date].[Month] ),
    "expression of total",
    IF( HASONEFILTER( 'Table'[Date].[Year] ), " expression of avg this year", "expression of avg all years" )
)

Please change the "expression" part to calculate what you want. For example, avg = AVERAGE('Table'[Value]).

You can calculate total or avg group by year , month via drill down ( marked in red).

Result:

vchenwuzmsft_0-1644374599035.png


Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

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

 

 

Thank you for your reply. I have actually edited the query and transposed the data in a way I get month-year and all the activites from a single query. I am no longer using the Date Query. Instead, I have used group_by to group it based on the activity dates and get the sum for each activities. Next step, I put conditional columns to get the number of sessions occured in a month-year by checking the sum column of that particular activity - i.e. if the sum is > 0 the output in conditional column in 1. Hence I get the number of sessions occured in a month - year. After that, I transposed the query and got columns as month-year and all activities in the row. Then I calculated Average using this Power Query - each List.Average(Record.FieldValues(_ & [Session Type = null])). It did work perfect! 

Hi @NKotak_Leecare ,

 

I'm glad you've solved your problem. Please mark it as a solution and anyone who has the same problem as you will soon find the answer.

 

Best Regards

Community Support Team _ chenwu zhu

TheoC
Super User
Super User

Hi @NKotak_Leecare 

 

Try to use the Matrix visual, not the table visual. Selected in the below snippet in red box. 

 

TheoC_1-1643932335392.png

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Yes, using Matrix I do get the average but the format is messed up. Plus, when we expand the exercise with year and months the average is not accurate. Below are the attached snapshots of it.

 

Thank you, Theo!

 

Matrix_Average Correct.pngMatrix_Average Incorrect.png

Also, I am not understanding how the average for the month is incorrect?  The Average column that you first presented (highlighted in red below) is the Total Average for All Months combined. It is not at the "month" level.  

TheoC_0-1643933621751.png

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Yes, you are right. The average is incorrect in the first mock. Ideally, the Average for Bed Exercise should have been 13/8, where 8 is the total number of months. But we are not getting that there. Here is the outcome of Average taken as measure which is still incorrect.

 

The other pic is how we ideally want the outcome to be like:Activties_Requirements.pngAverage_Measure.png 

Hi @NKotak_Leecare 

 

I think the following measure will provide you what I believe you are after:

 

Measure = CALCULATE ( AVERAGE ( 'Table'[Amount] ) , ALLEXCEPT ('Table' ,'Table'[Session Type] ) )

Hope this helps.

Theo

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Yes, I tried it but the outcome is same as before. Here is the attached snapshot of it.New_Avg_Meassure.png

Hi @NKotak_Leecare 

 

Can you please provide a snapshot of the Matrix table and the values you are using in each of the fields? I think there is some kind of anomaly in what you are wanting in your output versus the current structure of fields being brought into your model.

 

Thanks,

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Required details:

 

Matrix_request.pngDate Table.pngSessions Type and Value.png

Hi @NKotak_Leecare 

 

Have you created the Average measure?  Measure = AVERAGE ( 'Table'[Amount Column] ) 

 

If you drag that into your value field of the Matrix, it should be correct.

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

Hi @NKotak_Leecare 

 

You will need to have a column that is properly formatted with a Date.  Currently, your date columns (i.e. Month Year) are suggested not to be in this state.  Do you have a Date table?  If not, please refer to here for a simple date table: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

Once you have established that, you can use the Date field hierarchy to drag the Year and Month properly into your table and that should allow you to get the dynamic outputs you are after.

 

I hope this helps 🙂

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Thank you, Theo! I have made Date table for it as well before. I tired using that to create the table but the outcome is not that useful. Below is the attached snapshot if it.Month_Year_Issue_SJE.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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