cancel
Showing results for
Did you mean:
Frequent Visitor

## Problems with totals for a measure

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])RETURNIF(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.
6 REPLIES 6
Community Support

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.

Frequent Visitor

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

Super User IV

@MrTryAndError , 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])

Proud to be a Super User!

Frequent Visitor

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.

Frequent Visitor

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
Frequent Visitor

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

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors