Static Totals or Averages

Question

Halo Guys&nbsp;So I desperately need help. Is it impossible to Calculate a static value in power bi. For example, lets say I have a value for each day and I calculate an average, everytime you place this average measure over dates on a line graph, it keeps on calculating the average for every single day, which just gives you the value for that day again.&nbsp;&nbsp;It seems everytime you plot this over date it SUMS the values for that day divided by 1 day(that particular day). I would like to calulate a static measure, meaning if I plot this measure over date, it gives me a straight line, life time average. How on earth do you do this?? I have already tried, ALL, ALLEXCEPT,... ect. Filter functions only help for filters in the query, it does not help for slicers or plotting. To give you another example, I once tried to calulate a static total for carats, but carats have different sizes, so I wanted to calculate the total carats for the entire month, because the purpose was to calculate the % carats for every single class over total carats for the month, but if I plotted this over the carat classes it gave me 100% for all classes, meaning, what Power bi did was total carats for every single class divided by total for that class and again with this, not any filter function worked.&nbsp;Please help

v-yuta-msft · Answer

Hi&nbsp;Kaldorr,
&nbsp;
Please give some sample data and measure you are using. In addtion, which requirement are you trying to achieve, can you clarify more details about your expected requirement?
&nbsp;
Regards,
Jimmy Tao

anonymous · Answer

See Pic below with highlighted column. The function I am currently using is,

CALCULATE(AVERAGE(Column), FILTER(Table, ALLEXCEPT(DATE)). The reason I did this is, because I want to plot the data points in that highlighted column with its average and standard deviation over date on a line chart, but once I plot the average over dates it just gives me the data points as in the column. So it seems power bi just sums the tonnes for that day and then average it over that day so It does not give me an average line around wich the data points can be shown. If I put this in a card it seems fine, but as soons as one puts it over dates on a graph it calculates for every single date seperately.

anonymous · Answer

Just a litte bit more on the average, the average and standard deviation must be for the FY, but the graph must be plotted over days since I want to show the data points per day around this average and standard deviation.

v-yuta-msft · Answer

Hi&nbsp;Kaldorr,
&nbsp;
Modify you calculate column using DAX formula like below and check if it can meet your requirement.
Result =
CALCULATE ( AVERAGE ( Table[Column] ), ALLEXCEPT ( Table, Table[Date] ) )

Regards,
Jimmy Tao

anonymous · Answer

Thank you for the reply. I should have added that the average must be over financial year, your function gives me the lifetime average, which I now realise I asked for, but it must be the average for the financial year, meaning FY18 will have a different average than say, FY16 and FY17.&nbsp;All that said, I did find a way, but it was quite a workaround. I would still love a measure, but this for now will do. For the record, I created a custom table with only one column with all the FY's, I then linked this with the FY column in my date table, I then used the measure Calculate(SUM(COLUMN), VALUES([FY])). If you then right this measure to a calculated column in the custom FY table I made, you get the total tonnes per FY, then I created a calculated column that gives the count of the FY's, by dividing those I got the average for each FY, from there on it becomes obvious I think.&nbsp;If you do have&nbsp; simple measure for me that would be great, considering the total mess above.

Forum Discussion

Static Totals or Averages

6 Replies

Related content

Total Min/Max measure where and keep the value static

% with static denominator

Total per month / average per month

Average and Moving Average

Row Total Average