Simple average in eazyBI

[Jeff Tillett]

Not sure why, but every time I try to use "eazy" BI versus something like excel, it's WAY harder. :( Feedback from our company is that this product should be called "hardBI" LOL. 

I am trying to just do a simple average of the numbers that show up in eazyBI. Right now I have this: 

And all I want to do is calculate the average of all my out of compliance numbers. In excel, I highlight the numbers and click average. 

On paper, I just add them all and divide by 9. 

In eazyBi, this is like finding the holy grail. Everything wants to use times and ranges and other measures and all this super complicated stuff, and all I need to do is X+Y/2.

I figure if I can get a total, then I can figure out how to average it, but I can only get a total with the "total" button, which I can't use in any calculations. When trying to create a calculated measure to make a total, I can't get anything that works. 

I tried this: 

Aggregate([Measures].[Out of Compliance])

but it just spits out the same number next to each row, and isn't actually aggregating anything, and all the examples I can find in google are way more complicated than grabbing a simple total.  I tried stuff like

Aggregate([Measures].[Out of Compliance].CurrentHierarchy.Members)

however this results in error messages.

Then I run into the problem of getting it to count all of the non-empty rows for the average calculation. When I try 

Count([Measures].[Out of Compliance].CurrentHierarchy.Members)

then I always get "1", even though I can plainly see 9 members.

I love the power of eazyBI, but it's so non-intuitive for basic needs, that I'm struggling to understand if I should continue to promote this tool and renew, or let it drop. :( 

[Lauma Cīrule · Accepted solution]

Hi Jeff,

I am truly sorry to hear about your struggles with eazyBI! I hope we can find the solution for this case and I am happy to assist with any other questions to get over the undeniably steep learning curve of multi-dimensional data model and MDX.

Unfortunately, the image you are referring to in the question does not load (only for me?) 

But for calculating the average of a measure over the values from visible rows there is a great, and this time actually easy, predefined calculation possible - you can click on the measure you are interested in and choose Add calculated > Average (https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Addstandardcalculationsbasedonaselectedmeasure)

Please let me know more details if you are looking to calculate the average of some filtered set!

Lauma / support@eazybi.com

[Jeff Tillett]

Thanks Lauma, I'll add this to my toolbelt for future uses, but I am still having one small issue with this. 

My "measure" that I am averaging is an aggregate of children, where I only want the average of the children. I had ended up manually creating a custom measure to perform the average, which is now helpful is validating this built in average function, and helped me to understand that the built in average tool is giving me the average of both the "total" sum of all children in addition to the children themselves. This is causing the average to appear as ~ double what it truly is. 

I would be most appreciate if you could help me solve for that. 

[Lauma Cīrule]

Jeff,

I'm happy to hear that the standard calculations will be useful for you!

For the follow-up question - the standard average is calculating an avg of all visible rows, including the All Crews and Squads row. You can see the formula that is used when you click on it and choose 'Edit calculated'.

If you would remove the first aggregating row and only have the values from the same level, you would get the average of those eleven rows. 

Let me know if there is anything else I can assist you with!
Lauma / support@eazybi.com

[Jeff Tillett]

So I would still need to continue to manually calculate my average as a custom measure since I can't modify the built in function to only include children in the calculation?

[Lauma Cīrule]

@Jeff Tillett,

Yes, the built-in formula cannot be modified and if you wish to exclude something from the set over which to calculate the average, it needs to be done in a separate calculation.

Nevertheless, you can copy the pre-calculation formula and paste in your new measure calculation and then add filter, case or other modifications.

Lauma / support@eazybi.com

[edwin vasquez]

EazyBI is not as easy as the name says it is lol
Wish I could help, but I need more practice myself.