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.
Dear Gurus,
I am trying to calculate an optimized demand for a set of products. The optimized demand depends on the count of locations that needs the product and under which area the location falls.
I am using the nested IF statment below below, but I keep getting blank value at the end. I have also tried SWITCH but to no avail
Optimized Demand =
SUMX (
'ProductDemand',
IF ([Loc Count] = 0,0,
IF('ProductDemand'[Area] = "Area1" && 'ProductDemand'[Area] = "Area2", [Loc Count]*2,
IF ('ProductDemand'[Area] = "Area3" && 'ProductDemand'[Area] = "Area4 && 'ProductDemand'[Area] = "Area5 && [Loc Count] > 0 && [Loc Count] < 3 ,
[Loc Count] + 1,
IF (AND ( [Loc Count] >= 3, [Loc Count] <= 5 ),[Loc Count] + 2,
IF (AND ( [Loc Count] > 5, [Loc Count] <= 9 ),[Loc Count] + 3,
IF ('ProductDemand'[Area] = "Area6" && 'ProductDemand'[Area] = "Area7” && [Loc Count] > 0 && [Loc Count] < 4 , [Loc Count] + 1, [Loc Count] + 2))), Loc Count] + 4)))
I am very new to DAX and power BI and I know that I am doing something wrong or that my approach is not working,
Thanks and sorry for the long post
HI @malamin,
After check on your formula, I haven't found any condition correspond to [Loc Count] +4, it seems wrong in your formula.
Optimized Demand = VAR t = [Loc Count] VAR condition = IF ( t = 0, 0, IF ( 'ProductDemand'[Area] IN { "Area1", "Area2" }, t * 2, IF ( 'ProductDemand'[Area] IN { "Area3", "Area4", "Area5" } && AND ( t > 0, t < 3 ), t + 1, IF ( AND ( t >= 3, t <= 5 ), t + 2, IF ( AND ( t > 5, t <= 9 ), t + 3, IF ( 'ProductDemand'[Area] IN { "Area6", "Area7" } && AND ( t > 0, t < 4 ), t + 1, t + 2 ) ) ) ) ) ) RETURN SUMX ( 'ProductDemand', condition )
BTW, since you are using complex nested condition formula in sum function to loop all rows, I think it will effect calculation performance.
Regards,
Xiaoxin Sheng
@v-shex-msft Thanks for the Reply!
I knew something was wrong!, What I am trying to do is:
Optimized Demand = SUM ( IF Loc. Count =0 THEN 0
IF Area 1 AND Area2 THEN Loc. Count *2
IF Area 3 AND Area4 AND Area4 AND 0<Loc Count<3 THEN Loc. Count +3
AND IF 3<=Loc. Count<=5 THEN Loc.Count +2
AND IF 5<Loc. Count<=9 THEN Loc. Count +3 OTHERWISE Loc. Count +4
IF Area6 AND Area 7 AND 0<Loc.Count<4 THEN Loc.Count +1 OTHERWISE Loc.Count +2
That's basically it.
Best Regards,
HI @malamin,
Obviously, it should more clarify than previous one, I modify my formula based on new conditions:
Optimized Demand = VAR _LOC= LoC.Count RETURN SUMX('ProductDemand', IF( 'ProductDemand'[Area] IN { "Area1", "Area2" }, _LOC*2, IF('ProductDemand'[Area] IN { "Area3", "Area4", "Area5" }, IF(_LOC>0&&_LOC<3,_LOC+3, IF(_LOC>=3&&_LOC<=5,_LOC+2, IF(_LOC>5&&_LOC<=9,_LOC+3,_LOC+4))), IF('ProductDemand'[Area] IN { "Area6", "Area7"}, IF(_LOC>0&&_LOC<4,_LOC+1,_LOC+2))))
)
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
Much thanks for your help.
However I have the same calculation in an excel sheet (that I used normally use for this calculation) but the result from PBI is significantly larger, but only for the filters for Area3 to Area7 (Calculation for Area1 and Area2 brings the same result).
Apologies for the hassle, but any idea why it could be so?
Regards,
Hi @malamin,
I think these formula may not fully works in sumx function.
For your scenario, I'd like to suggest you if condition part formula to create new calculated column with replace loc count value, then summarize based on new column instead to direct summarize and replace current loc count in sumx function at same time.
Regards,
Xiaoxin Sheng
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 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |