Max value for group of data

[MikoMan]

Hi,

I have table which holds interfaces throughput.
The table holds the time stamp, interface name an throughput value.

i need to summarize for each time stamp the total of those 3 interfaces, the find the maximu, vlaue of this claculation for the time period of my filter . ( 1 day )

See the "total calc" column which suppose to show this Summarized value , show something not right.

For this calculation i used :

    total(
   [Interface_Health].[f_Virtual_InterfaceMetrics].[Throughput Out (bps) (avg)]
    for [Interface_Health].[d_Interface_Health_Time].[5 Minute]
)

but something is wrong.

Any idea ?

Thanks

Miko

[hespora]

Your query is definitely double-counting something here. Might just be coincidence, but in the first interval in your screen, the calculated total is exactly twice the amount it should be. Unfortunately, I cannot right now find a pattern in the other blocks.

Could it be that [Interface_Health].[f_Virtual_InterfaceMetrics].[Throughput Out (bps) (avg)] is not a fact but a calculation (like the avg already suggests)? In RS, right-click on your data item in the data source, click on lineage, click on technical view, and check what it says under type and expression at the bottom.

[MikoMan]

HI hespora,

i did what you asked me for, here is the data:

Property    Value
ID           [Interface_Health].[f_Virtual_InterfaceMetrics].[Throughput Out (bps) (avg)]
Name     Throughput Out (bps) (avg)
Type           Query Item
External Name   Throughput Out (bps) (avg)
Datatype   Float 64
Precision   0
Scale   0
Size   8
Regular Aggregate   Average
Semi Aggregate           Sum

[hespora]

Hi Miko,


my *assumption* is that the regular aggregate: average is the problem here.

What you can do is create a subquery: First, remove your total formula from your existing query. Go to the query explorer and drag in a new query object. Then, drag your already existing query to the right of the new one. Double click on the new query, drag in all data items that you have, and in here, create a new data item with your total formula. That should work.

[MikoMan]

Hi hespora,

I tried what you suggested , it didn't worked :(

Any other ideas ?

Best Regards,

Nissim