Rows as filters

[munnawar]

I have requirement as pasted below  

I should get one Exchange rate as one column/Filter in front end with filter option as Exch Rate1,Exch Rate2,Exch Rate3

My value column should calculate based on filter selected from Exchange rate , For ex Value*Exch Rate1 when I select Exch Rate1 from filter and Value*Exch Rate2 when I select Exch Rate2 so on.

Month	Brand	Actual	Budget	Exch Rate1	Exch Rate2	Exch Rate3
Jan20	AA	56	70	0.2	0.4	0.7
Feb20	BB	60	75	0.3	0.5	0.6
Mar20	CC	75	80	0.1	0.2	0.3

[Ivan_Bozov]

You need to transform the data a bit:

Data:
LOAD * INLINE [
Month|Brand|Actual|Budget|Exch Rate1|Exch Rate2|Exch Rate3
Jan20|AA|56|70|0.2|0.4|0.7
Feb20|BB|60|75|0.3|0.5|0.6
Mar20|CC|75|80|0.1|0.2|0.3
] 
(DELIMITER IS '|');

Table_1:
LOAD
    Month,
    Brand,
    Actual,
    Budget
RESIDENT Data;

Table_2:
CROSSTABLE(Exchange_Rate,Value) LOAD
    Month,
    [Exch Rate1],
    [Exch Rate2],
    [Exch Rate3]
RESIDENT Data;

DROP TABLE Data;

Then you just need a straight table and a filter on the front end: