Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
_96bp
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:

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 🙂

1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
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 = 
    ADDCOLUMNS(
        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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

1 REPLY 1
Sahir_Maharaj
Super User
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 = 
    ADDCOLUMNS(
        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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors