Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
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
@MrTryAndError , based on what I got, you are using hasonevalue in this, that will not give value in total
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |