Forum Discussion

_96bp's avatar
_96bp
Regular Visitor
2 years ago
Solved

Calculate next highest Part in same Case by Amount

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 🙂

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    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])

     

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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])