Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Static Totals or Averages

Halo Guys

 

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. 

 

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.

 

Please help

6 REPLIES 6
v-yuta-msft
Community Support
Community Support

Hi Kaldorr,

 

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?

 

Regards,

Jimmy Tao

Anonymous
Not applicable

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.

 

image.png

Hi Kaldorr,

 

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
Not applicable

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.

 

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.

 

If you do have  simple measure for me that would be great, considering the total mess above.

Anonymous
Not applicable

Oh and I did also tried CALCULATE(AVERAGE('COLUMN), ALLEXCEPT('DATE', 'DATE'[FY])), the average does change from year to year, but it is not correct, because it does not correspond witht he average in the data.

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.