Calculate next highest Part in same Case by Amount

[_96bp]

Hi,

I have a table 'MP' that stores the following:
[ID]: a case ID

[MNr]: a material ID

[Amount]: the amount of material used
Within each case, multiple different materials can be used with varying amounts.
The table can look like this:
ID   |   [MNr]  |   [Amount]
1     |   11       |   4
1     |   10       |   2
1     |   12       |   25
1     |   20       |   20
2     |   20       |   20
2     |   5         |   6
2     |   10       |   30
2     |   8         |   1
3     |   10         |   7
3     |   3         |   8
3     |   22       |   10
4     |   14       |   1
4     |   20       |   3
I am currently showing the sum of [Amount] per [MNr], which is pretty straightforward.
So, as an example, [MNr] 20 would have the [Amount] 43 (20 from case 1, 20 from case 2, 3 from case 4).

Now, assume i select a certain material [MNr].
I want to obtain the material [MNr] that is changed most often together with the selected material within the same cases.
To continue the example from above, when selecting/showing [MNr] 20, the calculation should return [MNr] 10 with an [Amount] of 32 ([MNr] 20 and 10 occur togehter in cases 1 and 2. The sum of [Amount] from [Mnr] 10 is 32).

Even though [MNr] 10 occurs in case 3 as well, it's not together with [MNr] 20 such that it should not count.

Currently, after many other failures, I am trying the following:

Top 1 add.MP =

VAR _rank =

ADDCOLUMNS(

    MP,

    "Rank", RANKX(ALLEXCEPT(MP,  MP[MNr]),

            CALCULATE(MAX(MP[Amount])), , DESC, Skip))

Var _top2 =

SELECTCOLUMNS(

    FILTER(_rank, [Rank] = 2),

    "LP", MP[MNR],

    "KDA", MP[ID],

    "first", MP[Amount] +0)

RETURN

SUMMARIZE(_top2, [MP])

However, the returned material does not behave properly and I am not able to identify a pattern.

If you have any questions, Im happy to answer.
Thank you in advance 🙂

[Sahir_Maharaj · Accepted solution]

Hello @_96bp,

Can you please try this DAX approach:

TopRelatedMaterial = 
VAR SelectedMNr = SELECTEDVALUE(MP[MNr]) -- Assuming there's a way to select a single MNr, like a slicer.
VAR RelatedMaterials = 
    CALCULATETABLE(
        SUMMARIZE(MP, MP[ID], MP[MNr], "TotalAmount", SUM(MP[Amount])),
        NOT(ISBLANK(MP[Amount])),
        MP[MNr] <> SelectedMNr
    )
VAR SharedCasesWithSelectedMNr = 
    CALCULATETABLE(
        DISTINCT(MP[ID]),
        MP[MNr] = SelectedMNr
    )
VAR FilteredRelatedMaterials = 
    FILTER(
        RelatedMaterials,
        MP[ID] IN SharedCasesWithSelectedMNr
    )
VAR SummarizedRelatedMaterials = 
    SUMMARIZE(
        FilteredRelatedMaterials,
        MP[MNr],
        "SumAmount", SUMX(FILTEREDRelatedMaterials, [TotalAmount])
    )
VAR RankedMaterials = 
    ADDCOLUMNS(
        SummarizedRelatedMaterials,
        "Rank", RANKX(SummarizedRelatedMaterials, [SumAmount],, DESC, Dense)
    )
RETURN
    MAXX(FILTER(RankedMaterials, [Rank] = 1), MP[MNr])