Totals equal 0

[AgataJ]

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 name	On Hand Qty	Demand Qty	Excess Qty	Excess Qty to 0 Demand	$ cost	Excess Qty to 0 Demand $	Location
Aaaa	2	0	2	2	10	20	1
Aaaa	50	10	40	0	10	0	2
Bbbb	1	1	0	0	10	0	1
Bbbb	10	0	10	10	10	100	2
Cccc	10	9	1	0	10	0	1
Cccc	5	5	0	0	10	0	2
			Totals I get	0		0
			Should be	12		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 name	On Hand Qty	Demand Qty	Excess	Excess Qty to 0 Demand	$ cost	Excess Qty to 0 Demand $	Location
Aaaa	2	0	2	2	10	20	1
			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

[AgataJ · Accepted solution]

I solved this issue by using sumx(values) in DAX.

Great explanations are here:

Obtaining accurate totals in DAX - SQLBI

Computing totals by summing visible values in DAX

[Anonymous]

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]

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]

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

[AgataJ]

Hi Albert, @Anonymous 

Late answer but I was trying to work this out, unfortunately no success. Attached is the PBI file with where I am now. I managed to get correct Total for all measurement that counts Quantity. Unfortunately, that can't be said about Sum ($) for Excess.

The current results I am getting for Excess $ and Excess to 0 Demand $ are: 235.71 and 110, whereas I expect 160 and 120.

I would appreciate your feedback.

Kind regards,

Agata

Forum.pbix