Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey there,
I'm trying to work out a pretty easy formula for a pretty easy value:
I have a company dimension, which holds legal entities with their respective groups:
example: Group 1 belongs to 3 entities - the whole information is saved in one table.
Now my problem:
I want to build something like a gauge-based KPI - which shows the actual summarized year sales amount for the selected lega entity in relation to it's groups sales amount:
SUM(Selected Entity Sales Amount)/(SUM(Group Sales Amount of selected entity)/COUNT(entites of group)
Any ideas?
Your suggestion will give me the overall total sales summarized... but thanks for your help 🙂
By now I found a solution for my problem... it's really hard implementing some "easy" business logic steps into this nice point & click adventure 😄
My solution looks like this:
IF
(((SUM({1<Client=P({$<Client>} )>} Umsatz)-Sum(Umsatz))<>0),Sum(Umsatz)/((SUM({1<Client=P({$<Client>} )>} Umsatz)-Sum(Umsatz))/COUNT(TOTAL CompanyKey)), 0)
Hello,
Have you tried using TOTAL <Field> ?
Sum(Amount) / Sum(TOTAL <EntityID> Amount)
Hope that helps.
I've tried TOTAL as well but it didn't turn out as what I wanted it to be 🙂
It's looks like TOTAL is also affected by the selection in the field list, as it allways shows me 100%.
But that's not what I want it to show.
I want it to compare the Sales Amount of the selected Entity with the avg sales amount for the whole group, where 100% is ok, 75% is bad and 100+% is good, by now the scale is all I could manage 😄
Is there a way to make objects inside a dashboard "independent", so they will only be effected by some, not all selections. In my case I want that all selections will be ignored except the company dimension.
P.Schmerbach wrote:Is there a way to make objects inside a dashboard "independent", so they will only be effected by some, not all selections. In my case I want that all selections will be ignored except the company dimension.
There are ways to keep your values agains the whole data. If TOTAL gets all values in expression, set {1} means all values in dimension, so combined should return what you want regardless the selection:
Sum(Sales) / Sum({1} TOTAL Sales)
Hope that helps.
Your suggestion will give me the overall total sales summarized... but thanks for your help 🙂
By now I found a solution for my problem... it's really hard implementing some "easy" business logic steps into this nice point & click adventure 😄
My solution looks like this:
IF
(((SUM({1<Client=P({$<Client>} )>} Umsatz)-Sum(Umsatz))<>0),Sum(Umsatz)/((SUM({1<Client=P({$<Client>} )>} Umsatz)-Sum(Umsatz))/COUNT(TOTAL CompanyKey)), 0)