Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi AgataJ ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Create measures

Excess = SELECTEDVALUE('Table'[On Hand Qty]) - SELECTEDVALUE('Table'[Demand Qty])
Excess Qty to 0 Demand = 
IF(
    SELECTEDVALUE('Table'[Demand Qty]) = 0, 
    [Excess],
    0
)
Excess Qty to 0 Demand $ = [Excess Qty to 0 Demand] * SELECTEDVALUE('Table'[$ cost])
Sum Excess = 
VAR _table = 
SUMMARIZE(
    'Table',
    'Table'[Unit name],
    'Table'[On Hand Qty],
    'Table'[Demand Qty],
    "Excess",[Excess]
)
RETURN
IF(
    ISFILTERED('Table'[Demand Qty]),
    [Excess],
    SUMX(_table,[Excess])
)
Sum Excess Qty to 0 Demand = 
VAR _table = 
SUMMARIZE(
    'Table',
    'Table'[Unit name],
    'Table'[On Hand Qty],
    'Table'[Demand Qty],
    "Excess",[Excess],
    "Excess Qty to 0 Demand",[Excess Qty to 0 Demand]
)
RETURN
IF(
    ISFILTERED('Table'[On Hand Qty]),
    [Excess Qty to 0 Demand],
    SUMX(_table,[Excess Qty to 0 Demand])
)
Sum Excess Qty to 0 Demand $ = 
VAR _table = 
SUMMARIZE(
    'Table',
    'Table'[Unit name],
    'Table'[On Hand Qty],
    'Table'[Demand Qty],
    'Table'[$ cost],
    "Excess",[Excess],
    "Excess Qty to 0 Demand",[Excess Qty to 0 Demand],
    "Excess Qty to 0 Demand $",[Excess Qty to 0 Demand $]
)
RETURN
IF(
    ISFILTERED('Table'[On Hand Qty]),
    [Excess Qty to 0 Demand $],
    SUMX(_table,[Excess Qty to 0 Demand $]))

Final output

 

Best regards,
Albert He

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



AgataJ's avatar
AgataJ
Helper II
2 years ago

Hi Albert,

Anonymous 

Thank you very much for your time writing an answer to me.

I have re-created all mesurements, and this is what I am getting, based on one unit example:

 

 

Total Supply Column is a sum of two Columns from another Table2, with relationship:

Table 1 (unit number) many to one Table 2 (unit number) . Total Supply is a sum of On Hand qty + On Order Qty. May it affect the measurements?

 

The $ and Totals values are correct.

 

Thank you again, 

Agata

 

PS. I am on my holiday from tomorrow, so apologize in advance for the possible delay in aswering.