Ratio between categories MtD, YtD

[Anonymous]

Hi,

I have som time series data:

that i would like to present in a table like this:

I can construct the MonthToDate measures:

MonthToDate = sum(Timeseries[Value])-Calculate(sum(Timeseries[Value]);LastDate(PREVIOUSMONTH('Timeseries'[Date])))

and i can also calculate the ratio as maeasure.

Ratio = Calculate(sum(Timeseries[Value]);Timeseries[Category]="TS1")/calculate(sum(Timeseries[Value]);Timeseries[Category]="TS2")

But I can not figure out how to put it all in one table like the one above. Do I have to manipulate the data in an earlier step or is there a better way to do this?

Best Regards,

Hans

[Ashish_Mathur]

Hi,

How have you arrived at the numbers in each of the 3 numeric columns?  What is the underlying logic?  Share some dataset that can be pasted in an Excel file.

[v-lili6-msft]

HI, @Anonymous 

Based on my research, you could try this way as below:

Step1:

Add a Category dim table

categories = UNION(Values(Example[Category]), ROW("Category","Ratio TS1/Ts2"))

Step2:

Create the relationship between Category dim table and basic table by Category column

Step3:

Add measre like this 

Measure = IF(SELECTEDVALUE(categories[Category])="Ratio TS1/Ts2",[Ratio],CALCULATE(SUM(Example[Value])))

Then drag Category from Category dim table in to visual

Best Regards,
Lin