Forum Discussion
C
I am trying to calculate the average per complexity and then use VISUALS to display MTD average, YTD average, and month's average for each complexity.
My data looks like this :
I have tried the normal AVERAGE for experience rating in a calculated column, but it gives me a total average but not sure then how to get the visuals I want.
I came across a similar post with the following solution, but it again gives me the average, how do I visually represent the MTD, YTD & complexity wise average.
Average Rating = CALCULATE(AVERAGE(Table[Rating]), FILTER(ALLSELECTED(Table), Table[Complexity] = MAX(Table[Complexity]))
PS: I am quite new to Power BI and just know the very basics of visualizations.
2 Replies
Anonymous , Not very clear. with a date table and time intelligence you can try MTD./YTD like
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090eTo get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Anonymous - I could probably point you to a bunch of different links for things but if you want a specific answer to your specific situation, you need to share sample source data as text and expected output from that sample data. It is the quickest way to get a resolution to your issue.
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
