Skip to Content
author's profile photo Former Member
Former Member

Any to any possible combinations to be displayed in Xcelsius???

I have a data as below

Country State Region Revenue

C1 S1 R1 10

C2 S2 R2 15

C3 S3 R3 20

C4 S4 R4 25

I want to display in Xcelsius Dashboard as below

Country State Region Revenue

All All All 70(total of All)

All All R1 XX

All S1 R3 XX

All S4 All XX

C1 S3 R2 XX

C3 S2 R4 XX

like so on......

I am using Country, State, Region on 3 ComboBoxes and for revenue using Gauge.

Very thankful if any solution is posted.

Regards

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 13, 2011 at 11:36 AM

    Hi,

    One simple way to do this is to create an extra column - I'll name it "COMBINED" - which contains a concatenation of Country state and region (e.g. a formula could be A3&B3&C3). Also at the top of the Country, State and Region Columns add an "All" item with no revenue value. Also create 3 new columns (these will be used in filtering out state / regions to prevent invalid selections. I'll name these State1, Region1 and Region2. In the first cell in each of these columns enter "All".

    	*A	*B	*C	*D	*E	*F	*G	*H	*I
    *1	Country	State	Region	Revenue	COMBINED	State1	Region1	Region2
    *2	All	All	All				All	All	All
    *3	C1	S1	R1	10	C1S1R1
    *4	C2	S2	R2	15	C2S2R2
    *5	C3	S3	R3	20	C3S3R3
    *6	C4	S4	R4	25	C4S4R4

    Please note the first row / column as marked with asterisks represents cell values.

    Insert your three combo boxes for Country, State and Region. Set all to insertion type filtered rows and set all to ignore blank cells on the bahaviour tab.

    Set up the combo boxes as follows:

    Country: General Tab - Labels = A2:A6, Insertion Type = filtered rows, Source Data = B2:C6, Destination = G3:H6

            Behaviour Tab: Type = Label, Item: = K2, Insert Selected Item: = K2

    State: General Tab - Labels = G2:G6, Insertion Type = filtered rows, Source Data = H2:H6, Destination = I3:I6

            Behaviour Tab: Type = Label, Item: = L2, Insert Selected Item: = L2

    Region: General Tab - Labels = I2:I6, Insertion Type = filtered rows, Source Data = I2:I6, Destination = M2

            Behaviour Tab: Type = Label, Item: = M2, Insert Selected Item: = M2

    So now K2 = selected country, L2 = selected state, M2 = selected region

    Now in the cell N2 enter the formula:

    =IF(K2="All","*",K2)&IF(L2="All","*",L2)&IF(M2="All","*",M2)

    This returns a concatenation of the selected items with an asterisk if "All" is selected.

    Finally to calculate the total revenue simply use the formula

    =SUMIF(E3:E6,N2,D3:D6)

    Sorry if I've overexplained things but it's difficult to know how much detail you'll need. Also the filtering to columns G:I is not strictly needed but without it a user could select any country / state / region combination even impossible ones.

    Hope this helps.

    Paul

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Paul,

      Thank you for your reply.

      I implemented the same as suggested by you. i also got it working, but may be I missed on 1 pt. though i remove the filter from G:I as suggested by you, i cant get the data as required, as i don't have aggregated data in table.

      For eg:

      My data is as shown:

      SBU, City, Cadre, %Emp

      CHEMICALS BUSINESS, B, Executive, 54

      CHEMICALS BUSINESS, A, Workmen, 45

      CONSUMER PRODUCT, A, Executive , 70

      CROP NUTRITION, A, Management, 85

      I dont have the combination of below in database:

      All, All. Workmen, 54

      CROP NUTRITION, All, All, 85

      All, B, All, 70

      Using your logic, am unable to get data with above combination. Please suggest how to go ahead with such issue.

      Regards,

  • Posted on Dec 14, 2011 at 08:03 AM

    Hi,

    I tried writing the same logic in Excel sheet as per ur requirement but I am not able to copy-paste here as it contains formula's. I can send it to your email id if you want.

    You will have to just connect the appropriate fields of excel sheet to your ComboBoxes and Gauge used in dashboard and i hope it will work.

    Regards,

    RJ

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 14, 2011 at 09:07 AM

    Hi,

    Email sent to the email ID mentioned above, check out and let me know if any doubts.

    Regards,

    RJ

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.