Aggr in pivot adds a null for dim value

[dana]

Hi People,

In order to resolve a total issue in average calculation, I use Aggr in my expression:

Sum(Aggr({<Service_Cost_Type = {'Service Parts'}>}
(Sum({<Service_Cost_Type = {'Service Parts'}>} Service_Part_Costs_Monthly)
/
Count({<Service_Cost_Type = {'Service Parts'}>} distinct Period))
,Cust,Part))

But this causes a null value for the Part dim.

It doesn't happen with the original expression:

Sum({<Service_Cost_Type = {'Service Parts'}>} Service_Part_Costs_Monthly)
/
Count({<Service_Cost_Type = {'Service Parts'}>} distinct Period)

I can resolve it using the AGGR expression only when SecondaryDimensionality =0,

but I would like to know what causes the missing dim.

Thanks in advance!

[BrunPierre · Accepted solution]

Hi, I suspect the Part field may contain blank/null/whitespaces values.

Check null or blank
If(Len(Trim(Part )) = 0, 'IsNullBlank, 'NotNullBlank') as FieldCheck.

The simplest solution in a table might be to suppress zero values.

[dana]

Hi @BrunPierre ,

Indeed, there was one empty row in my data that caused it.

Thanks so much!