Average and calculated dimensions

[mruehl]

Hey,

I want to show the Average Stock Quantity of the Top 5 suppliers.
Top 5 means the 5 suppliers with the highest Average Stock Quantity.

The calculated dimension works as expected. I get the 5 suppliers with the highest average Stock Quantity.

This expression works fine for the non-calculated dimension on the right side:

But on the left side, the value of "Sonstige" (others) is not correct.

Where is my mistake? 🤔

[Rohan · Accepted solution]

Hi @mruehl ,

Try this once :

=if(dimensionality()=0,
avg(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge)),%DATEKEY_BESTAND)),
avg(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge)),%DATEKEY_BESTAND,HauptLieferant))/
Count({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>} Distinct HauptLieferant))

I believe this should do the trick. Let me know if it works for you.

Regards,

Rohan.

[mruehl]

@Rohan 

Thanks a lot, it worked nearly perfect.
Calculating the average not by using the avg()-function did the trick.

I just had to do some small modifications:

if(

dimensionality()=0,

sum(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge),%DATEKEY_BESTAND)),

sum(aggr(sum({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>}Bestandsmenge),%DATEKEY_BESTAND,HauptLieferant))

/

Count({<%DATEKEY_BESTAND={">=$(=addmonths(vToday,-12))"}>} Distinct [%DATEKEY_BESTAND]))

I had to change the count() from supplier to the date dimension. So it now counts the months.
An when I divide by the amount of relevant months, I have to sum up the inventory, not calculting the average.