Forum Discussion
Matrix drill up and down dates manipulating measures
I would like to create automatic matrix with drill up/down.
My data source consists of values for indexes for each date, so I have to create measure for 1/3. I created a measure:
1/3=Divide(
Calculate(sum([Index]),Filter([Index]=1),
Calculate(sum([Index]),Filter([Index]=3))
But when I drill up to months I get wrong answers. When I drill up I want fractions to count first sum of indexes and then their division.
Can someone tell me what I am doing wrong?
Please help 🙂
Hi ,
Based on the file you sent What you can do is to create a new table with the indexes and a row for that specific 1/3.
In this case I have created a rtable based on the values of your table 1 because you need to have clean data without that filter now create the following code for the table:
Index = UNION(DISTINCT(Table1[Index]), ROW("Index", "% 1 z 3"))Add the following measure to your model:
Index total = SWITCH ( SELECTEDVALUE ( 'Index'[Index] ), "% 1 z 3", FORMAT ( DIVIDE ( CALCULATE ( SUM ( Table1[Metric] ), Table1[Index] = "1" ), CALCULATE ( SUM ( Table1[Metric] ), Table1[Index] = "3" ) ), "#.00%" ), CALCULATE ( SUM ( Table1[Metric] ), Table1[Index] IN DISTINCT ( 'Index'[Index] ) ) )
6 Replies
- MFelix
Super User
Hi alamakota97 ,
Is this a column or a metric? Seems to be a column this need to be calculated based on a measure.
I dont' know if I understood your question correctly. Number of Inexes I put in rows and metrics for each index in values.
I have data source:
in columns:
Index,Metric, Date, month,year
And I want to create measure for fraction 1/3 and to have indexes and measures in rows in matrix like I have shown in the picture.- MFelix
Hi alamakota97
The formula you present is for a measure or for a column or a line value on your data if you create a measure with the following code you will get expected result:
1/3 = Divide( Calculate(sum('Table'[Index]),'Table'[Index] = 1), Calculate(sum('Table'[Index]),'Table'[Index] = 3))
