Matrix column based on other matrix columns

[markmsc]

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.

[Irwan · Accepted solution]

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.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to implement calculation group.

[Ahmedx · Accepted solution]

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 this

[Kedar_Pande · Accepted solution]

@markmsc 

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.

[markmsc]

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.

[markmsc]

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.

[Ahmedx · Accepted solution]

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 this

[markmsc]

Hi again @Ahmedx .  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.

[markmsc]

Hi @Ahmedx .  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.  

[v-aatheeque]

Hi @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.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to implement calculation group.

[markmsc]

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.

[markmsc]

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.  

[markmsc]

Here are screenshots rather than Excel pastes of the data tables in case these are easier to read:

[Irwan · Accepted solution]

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.

[markmsc]

Hello again @Irwan .  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 @Kedar_Pande 's as given in another reply.  But you 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.

[markmsc]

Hi @Irwan .  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.