Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EnrichedUser
Helper III
Helper III

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

 

 

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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.

Jihwan_Kim
Super User
Super User

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

 

Picture3.png

 

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

Appreciate your Kudos!!

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors