Forum Discussion

AgataJ's avatar
AgataJ
Helper II
2 years ago
Solved

Totals equal 0

Hi,

I have spent a while trying to solve this problem, searching through Forum but haven’t found the answer applicable to my case.

 

I am trying to calculate the Excess to 0 Demand for each unit.

 

I need to know the Unit’s Qty/$ if the Demand for it equals 0.

Also, I need an overall Excess to 0 Demand and Excess to 0 Demand for each location.

 

Table below shows the data:

 

Unit nameOn Hand QtyDemand QtyExcess QtyExcess Qty to 0 Demand$ costExcess Qty to 0 Demand $Location
Aaaa202210201
Aaaa50104001002
Bbbb11001001
Bbbb1001010101002
Cccc109101001
Cccc55001002
   Totals I get0 0 
   Should be12 120 

 

DAX used goes this way:

 

Excess = On Hand Qty - Demand

Excess Qty to 0 Demand = if([Demand]=0, [Excess Qt],0)

 

Excess Qty to 0 Demand $ = Excess Qty to 0 Demand * standard cost

 

If I filter the Table by Item number, I am getting correct Totals:

Unit nameOn Hand QtyDemand QtyExcessExcess Qty to 0 Demand$ costExcess Qty to 0 Demand $Location
Aaaa202210201
   Totals:2 20

 

 

But If the Table is not filtered at all, I am getting Totals = 0.

 

I appreciate any idea how to make correct Totals.

 

Thank you,

Agata

Need Help

5 Replies

  • 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi AgataJ ,
        Thank you for your reply, the above method is applicable to the example data you provided earlier. Regarding the new question, please provide detailed example data including the two tables you mentioned and the relationship between them so that we can help you faster. Please hide sensitive information in advance.

        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