Hello All,
I have two tables. Table A is a dimension tables although it has Metrics in it (Aggregated table). It is Joined to Fact Table B on key1, key2, key3. Then a logical column is created using columns from two tables. A case statement is written in such a way that it will calculate "metric XX" as (sum(quantity)/Metric1) or (sum(quantity)/Metric2) or (sum(quantity)/Metric3). Another time dimension is joined to Fact table B. Using that "Metric XX previous month" (Ago("Business Model"."Table B"."Metric XX" , "Business model"."Time Dimension"."Month" , 1).
when Month from time dimension and the Metric XX from table B is pulled Into the analysis it's working fine but when Metric XX previous month is added the results are not as expected. They are duplicating as in the pic below. Please take a look and let me know if you know why it is not returning the correct values.
PS: I calculated the Metric XX previous month in the analysis then also it's not working. I tried calculating the previous month calculation using ago for Sum(Quantity) column, it's working fine. So I'm thinking the metric XX is not returning the Expected values because Table A is not joined with time dimension. Correct me if I'm wrong.
Table A:
| Key1 | Key2 | Key3 | Metric1 | Metric2 | Metric3 |
|---|
| 123 | A | AAA | 1 | 2 | 3 |
| 234 | B | BBB | 2 | 3 | 4 |
| 345 | C | CCC | 3 | 4 | 5 |
Table B:
| Key1 | Key2 | Key3 | Sum (Quantity) |
|---|
| 123 | A | AAA | 10 |
| 234 | B | BBB | 20 |
| 345 | C | CCC | 30 |
