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

[Former Member]

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

[raj_sharma]

Hi,

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

Regards,

RJ

[raj_sharma]

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

[Former Member]

Hi,

You can send to this mail id. "chkr123@gmail"

Regards

[Former Member]

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

[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,