Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
It's Friday afternoon, I'm tired, and I can't think of the correct answer so I'm hoping someone here can put me straight;
How do I perform calculations within a dimension? For example, let's suppose I have a country dimension in my database. How do I compare one country to another?
Eg:
France <--- from database
Germany <--- from database
Spain <--- from database
Spain as % of (France+Germany+Spain). <--- this is calculated.
(I'm actually trying to re-create a P&L with margin calculations;
Sales,
Commission,
Commission as % of Sales
the dimension is the lines of the P&L, the measures are "Actuals", "Forecast", etc.)
If France, Germany and Spain are the three selected (or possible) countries, you can create a pivot table that shows Sum(Sales) / Sum(total Sales). This will be the percentages.
HIC
If you actually wanted to find the differences between one country and another you could use an extension (like qsVariable but I belive there are others) and create variables vCountryA and vCountryB to setup a sheet that would allow to compare like with like.
sum({$ <Country={$(vCountryA)}>} Yourmeasure) Vs sum({$ <Country={$(vCountryB)}>} Yourmeasure)
Hope this helps although on a Friday I may have just complicated this
Regards
Andy