Forum Discussion
Matrix column based on other matrix columns
Hello colleagues -- I have some data that I need to turn into a matrix in one or more steps, but I'm a bit unclear on how to build up to the final result. Let's say this is my dataset:
City | Color | Fruit | Tree | Num1 | Num2 |
Chicago | Red | Apple | Elm | 2 | 4 |
Chicago | Red | Pear | Elm | 3 | 2 |
Chicago | Blue | Melon | Elm | 5 | 2 |
Chicago | Blue | Apple | Elm | 3 | 6 |
Chicago | Blue | Pear | Oak | 4 | 8 |
Chicago | Green | Melon | Birch | 7 | 7 |
Chicago | Green | Apple | Birch | 1 | 2 |
Chicago | Green | Pear | Birch | 3 | 6 |
And I want a matrix that calculates as follows:
| Apple | Pear | Melon | Elm | Oak | Birch |
Chicago | Sum(Num1) | Sum(Num1) | Sum(Num1) | Sum(Num2) / (Sum(Apple-Num1) + Sum(Pear-Num1)) | Sum(Num2) / (Sum(Melon-Num1) | Sum(Num2) / (Sum(Apple-Num1) + Sum(Pear-Num1)) |
So my final result is:
| Apple | Pear | Melon | Elm | Oak | Birch |
Chicago | 6 | 10 | 12 | =(4 + 2 + 2 +6) / (6 + 10) | = 8 / 12 | = (7 + 2 + 6) / (6 + 10) |
The Apple-Pear-Melon part of this is academic, obviously. But I'm not sure how to get to the Elm-Oak-Birch part since those require the sums of Apple, Pear, and Melon as inputs.
I hope this conceptual example makes sense. Can someone help explain how I can build up from my raw data to this final result? Thank you very much.
Edit: Sorry if the message formatting makes the tables a little hard to parse.
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.
14 Replies
Hi,
Please check the below picture and the attached pbix file.
I tried to implement calculation group.
Hi Jihwan_Kim . 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 will follow up again soon.
Hi again Jihwan_Kim . I ended up using another approach to solve my issue, but I tested yours and agree that it definitely works. And I learned something new! Accepting as a solution, with my thanks for your time.
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 thisHi markmsc
Just checking in to see if the previous response helped resolve your issue. If not, feel free to share your questions and we’ll be glad to assist.
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.
Here are screenshots rather than Excel pastes of the data tables in case these are easier to read:
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.
