Forum Discussion

troyhimes's avatar
troyhimes
Icon for Resolver I rankResolver I
4 years ago
Solved

Total Calculation based on Total/Day for given Month

Can't seem to work this one out....   I have a table that has budget amount per day for a given month with a date value in the format month/1/year per Area.  Data below is for 'Area 1'   Us...
  • tamerj1's avatar
    tamerj1
    4 years ago

    troyhimes 
    I Plead guilty 🙂
    You are absolutely right. I overlooked the data and did not pay enough attention. I did not notice that the Tons/Day values are only in the first day while in other days are blank which is actually your original problem. 
    Please refer to updated file with the solution https://www.dropbox.com/t/RYcaBXfL2RcdQd5S
    The code is little long. I know it can be shorter and more simple by using GENERATE function but I personally still did not learn how to use it properly. Also can be solved by adding a new calculated column. Pretty sure other methods are available out there. However this one seems to work

     

     

    MMP_Tons = 
VAR Table1 = 
    SUMMARIZE (
        Actuals_and_MonthlyPlans,
        Actuals_and_MonthlyPlans[Area],
        'Calendar'[YYMM],
        "@Tons", SUM ( Actuals_and_MonthlyPlans[MMP_Tons/Day])
    )
VAR Table2 =
    SELECTCOLUMNS ( 
        Table1,
        "@YYMM", 'Calendar'[YYMM],
        "@@Tons", [@Tons]
    )
VAR Table3 =
    CROSSJOIN ( 'Calendar', Table2 )
VAR Table4 =
    FILTER ( Table3, [YYMM] = [@YYMM] )
VAR Result =
    SUMX ( Table4, [@@Tons] ) 
RETURN
    Result

     

     

    Please let me know if this solves your problem. Have a nice day!

troyhimes's avatar
troyhimes
Icon for Resolver I rankResolver I
4 years ago

amitchandak You're right, I wasn't very clear!  I forgot to include the "measure below". 

I scanned your linked post and will check it out closely in the morning.  Prior to the measure above, I've determined the value per day using a technique similar to yours, but maybe the piece that I'm missing in the measure is the 'TotalMTD' for my day count(?)

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

troyhimes .
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

because TotalMTD blog I shared. I need to try with data

  • troyhimes's avatar
    troyhimes
    Icon for Resolver I rankResolver I
    4 years ago

    amitchandak 

    PowerBI Question.pbix 

    Here it is. The measure in question is 'MMP_Tons'.  The plan per day for the given month is 'MMP_Tons/Day' and the date I've associated with that plan is always the 1st of the month.  For a given time period I want to calculate the planned tons based on the plan per day.