Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I have created a text box that shows my 3 best product sales increases for a given month compared to the previous month and this for a specific region.
My calculation is the following :
-----------------------------
concat(
IF(
aggr(
Rank(
(Avg({$<[Region]={"A"}>}[Sales])-Avg({$<[Region]={"A"}, Month = {"$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))),
[Product])<=3,
[Product] & chr(10)))
-----------------------------
This works fine but does not show the increase in sales.
Any idea how I could show this calculation in here :
[Product] & ' (' & ??? &')' & chr(10)
Thanks in advance for your input!
Cheers
May be this
Concat(
If(Aggr(Rank((Avg({$<[Region]={"A"}>}[Sales]) - Avg({$<[Region]={"A"}, Month = {"$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))), [Product])<=3,
[Product] & '(' & Num(Aggr((Avg({$<[Region]={"A"}>}[Sales]) - Avg({$<[Region]={"A"}, Month = {"$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales])), Product), '#.#%') & ')'), Chr(10))
May be this
Concat(
If(Aggr(Rank((Avg({$<[Region]={"A"}>}[Sales]) - Avg({$<[Region]={"A"}, Month = {"$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))), [Product])<=3,
[Product] & '(' & Aggr((Avg({$<[Region]={"A"}>}[Sales]) - Avg({$<[Region]={"A"}, Month = {"$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales])), Product) & ')'), Chr(10))
Thanks Sunny, this works.
And in case my sales would be a %, how can I format it to have it shown as % instead of 0,...... ?
May be this
Concat(
If(Aggr(Rank((Avg({$<[Region]={"A"}>}[Sales]) - Avg({$<[Region]={"A"}, Month = {"$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))), [Product])<=3,
[Product] & '(' & Num(Aggr((Avg({$<[Region]={"A"}>}[Sales]) - Avg({$<[Region]={"A"}, Month = {"$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales])), Product), '#.#%') & ')'), Chr(10))
Hi Sunny,
Your suggestion worked fine although there is a small mistake in your formula.
Concat(
If(Aggr(Rank((Avg({$<[Region]={"A"}>}[Sales]) - Avg({$<[Region]={"A"}, Month = {"$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales]))), [Product])<=3,
[Product] & '(' & Num(Aggr((Avg({$<[Region]={"A"}>}[Sales]) - Avg({$<[Region]={"A"}, Month = {"$(=Date(AddMonths(Month, -1), 'MMM-yy'))"}>} [Sales])), Product), '#.#%') & ')'), Chr(10))
If you can adapt your post, I can mark it as "correct". 🙂
Thanks again!
(Sorry for the autocorrect on your name)
Name is Sunny.... I am glad you were able to get it fixed.... Please close the thread by marking the correct and helpful responses.
Best,
Sunny
Hi Sunny, see my updated post.
Sure