Forum Discussion
Divide value (average) from all months by first filtered value (average)
Hi,
I need your help.
I have different timeseries, in this example data I have two different series (id = 1 an did=2). They have different periodicity, therefore i computed the average value to show for each month (for example, id=2 has two values per month, and only the average is shown). Now I have the average value for each month by each series.
I want to divide each average value by the first average value, but dynamically, the first value has to change when I filter on another time period.
So far I have created a measure which is partly what I want. It is correct, for the first series, the secon, third, fourth values are each divided by the first. But the values of the secon series are also divides by the first value of the first series. But here, I want that the second, third,.... value of the second series, is divided by the first value of the second series.
I would be very happy, if someone can help me solve this problem.
value value_date id
|
6 Replies
- bolfri
Solution Sage
I 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_RETURNI 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,
thank you fpr your help!
I think I may have explained it wrong what I want. So far your measures return the first value of every month, I think. But I need the first average of each id, and then divide every following by that. here is an example table of the results I expect:
