topic Re: Total for an averaged data element in SAS Visual Analytics

Total for an averaged data element

Genesis007 — Wed, 26 Oct 2022 08:53:38 GMT

Hi everybody,

I need some help for one report I made.

in the column highlighted in yellow I have a data item that contains the AVG function.

When I display the Total, the system give me an average of all my datas, and I would like the sum of them.
How can I obtain this result ?

Thanks in advance.

Michel.

Re: Total for an averaged data element

MarkusWeick — Wed, 26 Oct 2022 09:04:59 GMT

Hi @Genesis007,

I am not sure fi I 100% understood, how your column is defined. But in the Data pane (here a screen shot out of Visual Analytics 7.5 you can select the column and set the "Aggregatiion:" to "Sum":

Best,

Markus

Re: Total for an averaged data element

Genesis007 — Wed, 26 Oct 2022 09:13:16 GMT

Hi Markus, thanks for your answer.
I'm sorry but my explanation is not clear enough. The column in yellow contains an aggregated field through an average. When I display the totals in my table, the tool shows me the total average, and in fact I just want the sum of all the values contained in my column.

Re: Total for an averaged data element

MarkusWeick — Wed, 26 Oct 2022 09:26:29 GMT

Hi @Genesis007, so the only number you want to have changed is the 495 in your example? (Replaced by something like 66.743?)

Re: Total for an averaged data element

Genesis007 — Wed, 26 Oct 2022 09:28:59 GMT

That's it ! 😉

Re: Total for an averaged data element

MarkusWeick — Wed, 26 Oct 2022 09:40:32 GMT

Could you please send a screen shot of the formula of the calculated item?

Re: Total for an averaged data element

Genesis007 — Wed, 26 Oct 2022 09:50:06 GMT

Here is my formula.

Re: Total for an averaged data element

MarkusWeick — Wed, 26 Oct 2022 10:50:53 GMT

It's a tricky one. As VA chooses the kind of total aggregation of the aggregated column automatically, the only chance is to "outwit" VA. In VA 7.5 I managed to do it in an example:

The "Calc" column looks as follows:

So:

column "Calc" contains aggregated Averages, as I choose an if-clause which is always true.

But in the total aggregation of the column the sum of all data is used and not the average.

It looks as VA chooses the kind of aggregation for the total aggregation, which is used last in the formula for the aggregated column.

As this is based only on observations and no SAS-documentation, I would use it only under conditions, where I can check the results before forwarding them.

Re: Total for an averaged data element

Genesis007 — Wed, 26 Oct 2022 11:37:34 GMT

I've tried your tips, but, sadly it's not working.

Re: Total for an averaged data element

MarkusWeick — Wed, 26 Oct 2022 11:59:14 GMT

Sorry, I was happy that I found it at least for VA 7.5

Re: Total for an averaged data element

MarkusWeick — Wed, 26 Oct 2022 13:59:38 GMT

@Genesis007 ,

I just posted a general question to my approach :

Controling the totaling method for aggregated colu... - SAS Support Communities

Maybe someone else knows about a general rule.

Re: Total for an averaged data element

HunterT_SAS — Wed, 26 Oct 2022 14:01:59 GMT

If you're using SAS 9.4 and Visual Analytics 7.5, there is not going to be a great solution here. It will not do a double aggregation like you're looking for (average first, then sum the averages). You would need to precreate this in another table so that the table has a data item for the average already, and then VA will treat it like any standard measure and sum it or whatever you wanted.

If you have Viya 3.5 or Viya 4, there are two ways to achieve this.

The first is to create an aggregated datasource that uses your calculated item for average. The new datasource that gets created in your report will effectively be a standard measure, then you can add it to some table and the totals will sum it by default. More details on aggregated datasource can be found here (link to VA 8.5 for example):
https://go.documentation.sas.com/doc/en/vacdc/8.5/vareportdata/n1o2pvak2xw7jln13a2mxkytdmm4.htm

The second method is to create a new calculated item using the AggregateTable function. This function allows you to essentially create an aggregated table behind the scenes for that calculation and use it within your report objects. For example using CARS data, let's say I want to get Average MPG for certain car types. If I create a normal aggregated measure like you've done:

Then I end up in the same spot as you, my total is actually an overall average and not the sum of the averages:

But using the AggregateTable function, I can do this:

And this is really saying I want a table using Type and MPG (Highway) where MPG is averaged, then I want to Sum that when used in tables and graphs. In that same example, it looks like this:

You can see the averages per type match, so that number is right, but totals are different. The AggregateTable calculated item will sum it up. This approach will require you to include a specific category in the calculation like Type in my example, but should be able to get you closer to what you're looking for as long as it includes all of those categories I see in your crosstab.

Re: Total for an averaged data element

MarkusWeick — Wed, 26 Oct 2022 16:52:05 GMT

Hi @HunterT_SAS,

now that I have understood (thanks to your explanation in the other post) that also “the total” is subject to the formula for the aggregated column I think I found a way to get the result desired by @Genesis007. I adjusted the formula again:

For the IF-clause I choose all values of the “Department” column, so the only line not included is the total line, for which I can choose the aggregation method in the else clause.

This gives me the desired result:

Best,

Markus

(Averages for all departments, but the sum, not the average, in the total.)

Re: Total for an averaged data element

acordes — Wed, 26 Oct 2022 18:14:20 GMT

Look at this
https://communities.sas.com/t5/SAS-Communities-Library/SAS-VA-Report-Example-Add-a-scope-to-an-aggregated-measure/ta-p/783253

Re: Total for an averaged data element

Genesis007 — Thu, 27 Oct 2022 09:57:13 GMT

Thanks so much, your solution is perfect.
I've used the second solution, and it's OK !

Re: Total for an averaged data element

Genesis007 — Thu, 27 Oct 2022 14:31:14 GMT

Big thanks to everybody who helped me 😉