cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## 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:

VAR _rank =
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.

1 ACCEPTED SOLUTION
Super User

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 =
SummarizedRelatedMaterials,
"Rank", RANKX(SummarizedRelatedMaterials, [SumAmount],, DESC, Dense)
)
RETURN
MAXX(FILTER(RankedMaterials, [Rank] = 1), MP[MNr])
``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Super User

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 =
SummarizedRelatedMaterials,
"Rank", RANKX(SummarizedRelatedMaterials, [SumAmount],, DESC, Dense)
)
RETURN
MAXX(FILTER(RankedMaterials, [Rank] = 1), MP[MNr])
``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors