Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have encountered a problem with my report. Im trying to show some KPIs for couple different warehouses where I base it on some variable cost and fixed variables with possibility to use a month slicer.
Warehouse 1 | Warehouse 2 | Comment | ||
Variable cost | Cost for storage | X | Y | Variable, and based on sum measure (Cost for Storage_m = CALCULATE(sum(Actuals[Amount_r]),Actuals[Cost type] ="Cost for Storage") |
Fixed cost | SQM cost | 12000 | 14000 | Fixed per warehouse |
Problem formula | Cost for storage/SQM | sum of X/12000 | sum of Y/14000 | Formula, which works fine when choosing one month but not when leaving month empty or multiple months |
Correct formula | Cost for storage/SQM | - if 3 months chosen = (X/3) / SQM cost | - if no months chosen = (Y/12) / SQM cost | Slicer needs to change the the Cost for storage/SQM formula to divide with count of how many months thats been chosen |
Solved! Go to Solution.
I found a solution where I fixed the Cost for Storage part (making it an average based on the chosen month slicer), which then made the Cost per SQM calculation correct
I found a solution where I fixed the Cost for Storage part (making it an average based on the chosen month slicer), which then made the Cost per SQM calculation correct
Hi @AlexXandyr ,
Not sure if I understood the question correctly but you need to adjust your values for the fixed part to be over the full selected months something similar to:
Cost for Storage / SQM = DIVIDE ([Cost for Storage], SUMX((Calendar[Months], 12000)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you, sorry for not being clear. Please see below where I try to explain in another way
The Cost per SQM is actually an average of the months chosen in the slicer.