cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrTryAndError
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)

 

image.png

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:
image.png
 
Sample for 1 material, to see what it looks like. Same material will appear on more than 1 row for every month.
image.png
 
I hope someone can help me. I guess one or more of my measures is wrong in some way.
6 REPLIES 6
Ailsa-msft
Community Support
Community Support

Hi @MrTryAndError 

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.

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
Super User IV
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])


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

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
 
image.png
I think the problem comes from Dead stock Q measure (maybe in combination with 24 months GR quant), but I'm not sure

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

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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.