Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I'm doing a table with two dimensions and around 11 or 12 measures, all the measures are displaying the correct data per dimension apart from one, can anyone see what i'm doing wrong and why this one isn't working. I test the numbers in a KPI with the set analysis filtered to just one value from the dimension and it's right, but when i take the filter off and drop the whole expression in a table, i hope to see all the correct numbers per row for the values in the dimensions, 9 other measures are working as they should apart from this one.
The TEAM={"*"} filter in the set analysis is not working in this measure, where it is in others.
Sorry guys i've pasted the expression wrong, this is the correct expression
I want to divide each row of the dimesion by everything in that dimension
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"},TEAM={"*"}>}Value)
/
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"}>}Value)
Are you looking to disregard any selection made in TEAM field? or are you trying to exclude rows where TEAM is Null?
For second option, what you have should work.
For first option, try this: TEAM = or just TEAM
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"},STHA_DESC={"NHS Dorset CCG"}>}Value)
/
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"},TEAM>}Value)
Hi Lak,
You do not have to specify TEAM={"*"} as your set modifier " 1 " it self refers to the entire data set i.e here TEAM={"*"} .
So try your expression removing it:
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"},STHA_DESC={"NHS Dorset CCG"}>}Value)
/
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"}>}Value)
Sorry guys i've pasted the expression wrong, this is the correct expression
I want to divide each row of the dimesion by everything in that dimension
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"},STHA_DESC={"*"}>}Value)
/
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"}>}Value)
May be this (with TOTAL)
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"},STHA_DESC={"*"}>}Value)
/
SUM(TOTAL {1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"},STHA_DESC={"*"}>}Value)
Are you looking for the TOTAL qualifier? Do you want to ignore the dimension?
As always, context is important, so please tell us the dimensions used and some more details of your data model.
Also some sample lines of data and requested results always help to understand.
TOTAL qualifier (to ignore Dimension2, but consider Dimension1) may look like
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"}>} TOTAL<Dimension1> Value)
/
SUM({1<PRODUCT={"xxxx"}
,SID_MONTH={"201509"}>}Value)
All i want to do is DIVIDE DIM 1 "A" SALES by total sales of A,B,C,D,E as a % as a measure
DIM 1 | SALES | MARGIN |
A | 36738 | 36.37% |
B | 3383 | 3.35% |
C | 30233 | 29.93% |
D | 10322 | 10.22% |
E | 20332 | 20.13% |
For the sample above, this expression would work:
Sum(SALES) / Sum(TOTAL SALES)
you will need to adapt the above expression to your scenario.
sales is a measure by the way not dimension.
I've tried your way, no figures are displayed
in your table have you tried DIM1 as dimension and Sum(SALES) / Sum(TOTAL SALES) as expression?