topic Re: How to convert non aggregated function into aggregated function in Visualization and Usability

How to convert non aggregated function into aggregated function

128718 — Fri, 27 Aug 2021 10:16:54 GMT

Hi Expert,

I am having the below expression which is working fine and having right values in KPI,

sum(aggr(max({<[Type]={'S01'},[num]={0}>}[Days]),[ID]))/count([ID])

Now I want to pull this measure into pivot table. Since the nested aggr and island dimension is creating trouble I want the expression changed into non aggregated measure.

sum( distinct {<[ID]={"=max({<[Type]={'S01'},[num]={0}>}Days)"}>}[ID])/count([ID])

While using the above expression it is returning huge value.

Please help me to attain the target.

Thanks in advance

Re: How to convert non aggregated function into aggregated function

stevejoyce — Fri, 27 Aug 2021 11:08:42 GMT

What are your pivot table dimensions? Replace your pivot table dimensions in the aggr parameter where you have ID

sum(aggr(max({<[Type]={'S01'},[num]={0}>}[Days]),[ID]))/count([ID])

Re: How to convert non aggregated function into aggregated function

128718 — Fri, 27 Aug 2021 11:46:58 GMT

Hello,

I am using the below codes

In load editor:

ListofColumns:
Load * INLINE [
Name
Ram,
Raju,
John,
Mike
];

Dim:
Load * INLINE [
Dim
1
2
3
];

In sheet:

Dimension1:Name

Dimension2:pick(Dim,Country,Product)

Measure:

sum(aggr(max({<[Type]={'S01'},[num]={0}>}[Days]),Dim,Name,ID))/count([ID])

I am getting value only for the first dimension(Name-'Ram',Dim-'Country')

Please guide me to find the solution

Re: How to convert non aggregated function into aggregated function

stevejoyce — Fri, 27 Aug 2021 12:29:30 GMT

What if you make your measure like below. I don't think you're going to be able to have 1 aggr function using this Dim column like that

pick(Dim,

sum(aggr(max({<[Type]={'S01'},[num]={0}>}[Days]),Name,Country, ID)),

sum(aggr(max({<[Type]={'S01'},[num]={0}>}[Days]),Name,Product, ID))

)

/count([ID])

Re: How to convert non aggregated function into aggregated function

128718 — Sat, 28 Aug 2021 11:30:03 GMT

Thanks for your reply,

Still it is returning Zero values except for "Ram" same as above issue

Re: How to convert non aggregated function into aggregated function

stevejoyce — Sat, 28 Aug 2021 14:08:56 GMT

Is there definitely data for these other Names for [Type]={'S01'},[num]={0}? Calculation does look ok to me.

Are you able to send a fuller data set, it was a bit limited what you sent.