Forum Discussion

EnrichedUser's avatar
EnrichedUser
Helper III
5 years ago

Return Specific Value from Measure

Hi All,

 

I have a running total percentage messure [Pareto] based on an inventory table

 

I want to return the largest value from the [Inventory Value] messure from the ItemID > 0.50 for messure [Pareto]

 

Example:

 

BranchIDItemIDInventory ValuePI ParetoInventory Rank1
DB015000837046361.20.3911
DB0150005614450000.6992
DB015000783774883.90413

 

Th expected output would be 5000, the [Inventory Value] that is largest and over 0.50 [Pareto]

 

[Inventory Sample]

BranchIDItemIDLotNumberQuantityMACTotal MAC
AT01500051583P8Z0B294= Quantity x MAC
AT01500051583P8Z0B71384 
AT015000515868CHHB72006 
AT01500051586BX4FB56 
AT01500051586B1DFB23006 
AT01500051586026FB2586 
CL01500051580K9PHB1001 
CL01500051580K9PHB2001 
CL20500051588YL2FB3508 

 

 

Pareto = 
var InventoryValue = [Inventory Value]
var ALLIV = CALCULATE([Inventory Value], ALLSELECTED(Inventory) )

return
DIVIDE(
    SUMX(
        FILTER(
      SUMMARIZE(ALLSELECTED(Inventory), Inventory[ItemID], 
      "Dollars", [Inventory Value]),
      [Dollars] >= InventoryValue ),
            [Dollars]),
            ALLIV)

 

 

 

Inventory Value = 
SUM(Inventory[Total MAC])

 

 

Need Help

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, EnrichedUser 

    Please correct me if I wrongly understand your point.

    In order to find higher than 50% Pareto && highest quantity ->

    Please check the below picture if it is what you needed.

     

    Outcome =
    VAR newtable =
    FILTER (
    SUMMARIZE (
    ALLSELECTED ( Inventory ),
    Inventory[ItemID],
    Inventory[Quantity],
    "@pareto", [Pareto]
    ),
    [@pareto] >= 0.5
    )
    RETURN
    MAXX ( newtable, Inventory[Quantity] )

     

     

    Did I answer your question? Mark my post as a solution!

    Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi EnrichedUser ,

     

    What is the relationship between your example and [Inventory Sample]? The two tables seem to be different.

     

    If I understand correctly, you want to create a measure named 'Pareto'. How do you get the percentage of your Pareto measure? Is Inventory Value divided by the sum of all Inventory Values?

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.