Need help with calculate formula

[narangsaab]

Hi All,

i am trying to calculate average of some values (learning index factor) for some some students over various months. I have another data visual where i am showing per month fees from a fees table.

My objective is :

1. To show that respective month average learning index when i select any particular month in Fees Visual.
2. To show latest month average learning index in rest state.

I was using the below formula

Activeindex = Calculate( Average('Student'[index]),filter('Student','Student'[MonthNo]=Month(Lastdate('Student'[MonthDate]))))

where monthno and monthdate are other fields.

Instead of calculating average for a particular month. It is calculating average over all the data.

However, when i select a particular month from fees visual it shows that respective month's index data.

Can someone help ?

[Anonymous]

Hi there.

@narangsaab, there are multiple problems with your formula. First of all, please do not fall into the trap that so many do. A one-table model in a sure way to fail miserably in Power BI. Please create a correct model first - fact table(s) and dimensions. Once you have, you'll have an easy time to create easy and fast DAX. Secondly, when there are no filters on our table, the measure is calculating Month(Lastdate('Student'[MonthDate]))) which means the month number (within the year) of the last visible date in the table. This is then used to filter the table. What you'll get is all the months with this number across all years. This is definitely something you don't want. Months must have unique id's across all years. Please do yourself a favour and create a Date dimension with all the required date/time designations. Also, please remember the golden rule of DAX: Never filter a table when you can filter a column.

Please learn about the importance of the star schema in Power BI: https://bit.ly/3kd8IIL

[amitchandak]

@narangsaab 

First of Create a date from MonthNO or Month Year

new column This month Avg
Activeindex = Calculate( Average('Student'[index]),filter('Student','Student'[MonthNo]=earlier('Student'[MonthDate])))
Activeindex = Calculate( Average('Student'[index]),filter('Student',Eomonth('Student'[DAte],0)=eomonth(earlier('Student'[MonthDate]),0)))

//Measures with a date table
MTD Sales = CALCULATE(Average('Student'[index]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(Average('Student'[index]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

[Greg_Deckler]

@narangsaab Are you putting this into a visual with the month column? Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

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.

[narangsaab]

HI @Greg_Deckler 

Thanks for responding to my query. I am not putting it in a visual with a month column

I have attached a sample data file below.

https://drive.google.com/file/d/1C23yaShx2VtDGVujXd3HJx4iYimiYYRY/view?usp=sharing

In the image below, i want to show the learning factor as average of the latest month (august in my case), but its giving average across the entire data set.

However in the image below, when i select a particular month, its filter context is correctly passed into the measure.

I am new to this and tried searching a lot of forums as to why this is happening but was not able to understand.

Regards,

Aman Narang