Forum Discussion
Matrix column based on other matrix columns
hello markmsc
please check if this accomodate your need.
create a couple measures for apple, pear, elm, oak, and birch.
Apple =
SUMX(
FILTER(
'Table',
'Table'[Fruit]="Apple"
),
'Table'[Num1]
)Pear =
SUMX(
FILTER(
'Table',
'Table'[Fruit]="Pear"
),
'Table'[Num1]
)Elm =
var _Elm =
SUMX(
FILTER(
'Table',
'Table'[Tree]="Elm"
),
'Table'[Num2]
)
Return
DIVIDE(
_Elm,
[Apple]+[Pear]
)Oak =
var _Oak =
SUMX(
FILTER(
'Table',
'Table'[Tree]="Oak"
),
'Table'[Num2]
)
var _Melon =
SUMX(
FILTER(
'Table',
'Table'[Fruit]="Melon"
),
'Table'[Num1]
)
Return
DIVIDE(
_Oak,
_Melon
)Birch =
var _Birch =
SUMX(
FILTER(
'Table',
'Table'[Tree]="Birch"
),
'Table'[Num2]
)
Return
DIVIDE(
_Birch,
[Apple]+[Pear]
)Hope this will help.
Thank you.Hi,
Please check the below picture and the attached pbix file.
I tried to implement calculation group.
Another variant
1) I created the DimFruitTree table in Power Query
3) you need to sort the FruitTree column by Index
2) The relationship is like thisFor Fruit columns (Apple/Pear/Melon):
Fruit Measure = SUM(Data[Num1])
For Tree columns (Elm/Oak/Birch):
Tree Measure =
VAR AppleSum = CALCULATE([Fruit Measure], 'Data'[Fruit] = "Apple")
VAR PearSum = CALCULATE([Fruit Measure], 'Data'[Fruit] = "Pear")
VAR MelonSum = CALCULATE([Fruit Measure], 'Data'[Fruit] = "Melon")
RETURN
SWITCH(
SELECTEDVALUE('Data'[Tree]),
"Elm", DIVIDE(SUM(Data[Num2]), AppleSum + PearSum),
"Oak", DIVIDE(SUM(Data[Num2]), MelonSum),
"Birch", DIVIDE(SUM(Data[Num2]), AppleSum + PearSum)
)Use these measures in your matrix with City as rows, and Fruit/Tree as columns.
For Fruit columns (Apple/Pear/Melon):
Fruit Measure = SUM(Data[Num1])
For Tree columns (Elm/Oak/Birch):
Tree Measure =
VAR AppleSum = CALCULATE([Fruit Measure], 'Data'[Fruit] = "Apple")
VAR PearSum = CALCULATE([Fruit Measure], 'Data'[Fruit] = "Pear")
VAR MelonSum = CALCULATE([Fruit Measure], 'Data'[Fruit] = "Melon")
RETURN
SWITCH(
SELECTEDVALUE('Data'[Tree]),
"Elm", DIVIDE(SUM(Data[Num2]), AppleSum + PearSum),
"Oak", DIVIDE(SUM(Data[Num2]), MelonSum),
"Birch", DIVIDE(SUM(Data[Num2]), AppleSum + PearSum)
)
Use these measures in your matrix with City as rows, and Fruit/Tree as columns.
Hi Kedar_Pande . Thank you very much for your detailed response. I wanted to reply to acknowledge your effort, and also to update that I need another day to evaluate this approach with my full dataset.
I do have one immediate follow-up question, though. You suggest using Fruit and Tree as columns. However these two do not have a hierarchical relationship so I'm not sure how that would work. I suppose I could make a columns dimension to hold the value of fruit and tree and use that.
Hello again Kedar_Pande . As you probably understood, my actual dataset is more complicated than the simple example I gave in this question, and so your solution didn't quite work with my data as you wrote it out. I ended up using a bit of a hybrid of your approach and Irwan 's as given in another reply. But your basic method is sound, and certainly applied to the specific example I gave. It directly led to the solution to my requirement. Marking as solution, with my thanks.
