Forum Discussion
Divide value (average) from all months by first filtered value (average)
Solution SageI hope I understood correctly what you meant. I tried to recreate the logic of your problem. I created a new month column in the table: month = STARTOFMONTH(SampleData[value_date]) which returns the start of the month for the data set.
Measure =
VAR MONTH_REF = CALCULATE(FIRSTDATE(SampleData[month]),ALLSELECTED(SampleData[month]))
VAR START_RETURN = CALCULATE([__average_value],FILTER(ALLSELECTED(SampleData[month]),SampleData[month]=MONTH_REF))
RETURN START_RETURN
I believe that dividing the average value by the measure will be easy for you, so I will show you how to determine the denominator.
The measure refers to the minimum average of the available periods for a given ID, so in the absence of filters it will be a value of 130 for ID: 1 and 477 for ID: 2 (the minimum reference for ID 2 comes from March)
If you filter individual months, the measure will refer to the minimum starting value for a given ID.
I hope that's what you meant. If not, tell us what should be included in the nominative case and in what case. Preferably specific numbers so that I understand what measure you expect.
Thank you for your quick response!
I believe this is the solution I am looking for. But for some reason I do not get thesame results as you. The calculated column "month" wgich returns the first days of everymonth is correct. But when i added your measure for the denominator, my values are still wrong.
If you look at my screenshot, in April it should say 498 for both, 02.04. and 20.04.? I don't really know why it is different to your solution.
Is there a possibility to attach my pbx file, maybe this could help, understanding the problem.
The goal is, to "normalise" the values, by deviding all the values by the first one, that all values are relative to the first one (wich should be 1). For example, the first filtert value is in februar 2022, the average for this month is 20, then the normalised one is 1 (20/20), the secon average value from march 2022 is 30, therefore the result should be 1,5 (30/20).
- bolfri
You can use wetransfer.com to send pbix file or you can send me file via email (PW).
Hi kathrin_44 ,
I think you can try code as below to create a measure.
Measure3 = VAR _MINDATE = CALCULATE ( MIN ( 'Table'[value_date] ), ALLEXCEPT ( 'Table', 'Table'[id], 'Table'[month] ) ) RETURN CALCULATE ( [_average_value], FILTER ( ALLEXCEPT ( 'Table', 'Table'[id], 'Table'[month] ), 'Table'[value_date] = _MINDATE ) )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- bolfri
Hi,
I apologize for the late response. Not only will I write you measures, but I will share good tips for your future projects.
The problem you encountered was that in the daily view, the average referred to the result from one day, and it should refer to the average from the entire month. Instead of repairing a single element, I will show you how it should be constructed correctly.
I deleted all the columns you created for dates: year, year month, etc. Instead, we will create an automatic calendar with all the dates in your project.dim_calendar = CALENDAR ( FIRSTDATE ( 'Table'[value_date] ), LASTDATE ( 'Table'[value_date] ) )
I will add one column that represents year month, but you can format it as you want.date_year_month = FORMAT(dim_calendar[Date], "'YY \MMM")
Create relationship between value_date and date from dim_calendar like this:
Measure = VAR min_date = CALCULATE( FIRSTDATE('Table'[value_date]), DATESMTD(dim_calendar[Date]) ) VAR start_return = CALCULATE( [Average], dim_calendar[Date]=min_date ) RETURN DIVIDE([Average],start_return)