Problems with totals for a measure

[Anonymous]

Hi.

I'm having a problem to get the total for a measure I've created.

The total show 0, and I really need the total.

I'm aiming to show the total for the measure to create a trend over time.

The visual Table looks like this (filtered on Month 2021-03)

My first 2 Measures (6 months cons, 24 months GR quant) show correct values on row level and totals.

Val.stock is a column from data table and is correct.

Dead stock Q measure and Dead stock Q 2 shows correct values on row level, but not on totals. Expected total = 112 054

Calc dead stock show correct on totals and so does m_Total 1, but they are not correct on row level anyway.

Dead stock quantity is what I really want to get (and then add value to it as well).

Dead stock = Actual stock quantity in current month - Received quantity last 24 months. (If Dead stock < 0, then it should be 0.)

And I want to be able to see it on material level.

My measures (some are just attempts to solve my issue):

24 months GR quant = 

Calculate (

Sum ( 'Stock data'[Val.stk(R)]),

// DATEADD (Data[Month], -6, MONTH)

DATESBETWEEN('Stock data'[Month],FIRSTDATE(DATEADD ('Stock data'[Month], -23, MONTH)),LASTDATE(DATEADD ('Stock data'[Month], -0, MONTH)))

)

Dead stock Q measure = CALCULATE(IF(CALCULATE(SUM('Stock data'[Val. stock])>[24 months GR quant]),CALCULATE(sum('Stock data'[Val. stock])-[24 months GR quant]),0))

Calc dead stock = CALCULATE(sum('Stock data'[Val. stock])-[24 months GR quant])

Dead stock Q 2 = CALCULATE(IF([Calc dead stock]<0,0,[Calc dead stock]))

m_Total 1 = 

VAR __table = SUMMARIZE('Stock data','Stock data'[Material],"__value",[Dead stock Q measure])

RETURN

IF(HASONEVALUE('Hedge stock Data'[Material]),[Dead stock Q measure],SUMX(__table,[__value]))

Samples of source data:

Sample for 1 material, to see what it looks like. Same material will appear on more than 1 row for every month.

I hope someone can help me. I guess one or more of my measures is wrong in some way.

[Anonymous]

Hi @Anonymous 

Could you provide your sample ? The data you provided is very complicated, and I have no way to completely restore it. If you can provide a sample, it might help us to deal with the problem better.

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Hi.

Do you mean a sample of the source file or the PBI-file?

And how should I provide it? I don't find any possible way to attach files...

I could paste directly from the excel-file (source), but it will not look good 

[amitchandak]

@Anonymous , based on what I got, you are using hasonevalue in this, that will not give value in total

m_Total 1 =

VAR __table = SUMMARIZE('Stock data','Stock data'[Material],"__value",[Dead stock Q measure])

RETURN

IF(HASONEVALUE('Hedge stock Data'[Material]),[Dead stock Q measure],SUMX(__table,[__value]))

Change to

m_Total 1 =

VAR __table = SUMMARIZE('Stock data','Stock data'[Material],"__value",[Dead stock Q measure])

RETURN

SUMX(__table,[__value])

[Anonymous]

Hi again.

Stuck with my problem, but I have tried 2 new measures.

The first one works and gives me the correct total, but it is not what I'm looking for.

I'm using a file without Stor.Loc now as well.

Calc dead stock 2 = CALCULATE(sum('Stock data w/o SLoc'[Val. stock])-[24 months GR quant 2])

The problem is that I get negative numbers when 24 months GR quant is bigger than Val.Stock.

So I want negative numbers to become 0.

I tried this measure for that:

Dead stock Q 3 = CALCULATE(IF([Calc dead stock 2]<0,0,[Calc dead stock 2]))

This gives me a Total of 0 again.

Any thoughts about that?

[Anonymous]

Thanks for your reply, but it doesn't work.

m_Total 2 =

VAR __table = SUMMARIZE('Stock data','Stock data'[Material],"__value",[Dead stock Q measure])

RETURN

SUMX(__table,[__value])

I get the same result as for m_Total 1 and it's only using Val. stock as value - GR in the actual month. It does not subtract 24 months GR quant

I think the problem comes from Dead stock Q measure (maybe in combination with 24 months GR quant), but I'm not sure

[Anonymous]

@amitchandak Any other suggestions? I'm really stuck with this one and would be super happy to be able to move on with the report.