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'

 

Using the per/day rate I'd like to calculate the total budget across any time period broken out by Area.  The measure below of course works for a single month across any Area, but understandably as soon as I filter across multiple months it fails.

 

The correct value should be 16124.58=(503.23 * 31 days in Jan.) + (524.45 * 1 day in Feb).

 

How do I accomplish this?

 

Thanks!

 

 

 

  • 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!

8 Replies

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

    troyhimes , Not very clear

     

    Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798

     

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290

    • 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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi troyhimes 
    Here is your file with the solution https://www.dropbox.com/t/lZdUKnpNp5E6wAXA
    It is actually simple. you just need to iterate over the table with right granularity which is in this case, the 'Calendar' table

    MMP_Tons = 
VAR SumMMPTonDay =
    SUM (Actuals_and_MonthlyPlans[MMP_Tons/Day] )
VAR Result =
    SUMX (
        'Calendar',
        SumMMPTonDay 
    ) 
RETURN
    Result

    Please let me know if this answers you query. Thank you

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

      Hi tamerj1 ,

      Thanks for your attempt, but it looks like this yields the same result as my original measure....

       

      Examples: 

      1. Date Filter=1/1/22 to 1/31/22, MMP_Tons/Day = 503.23, MMP_Tons = 15,600.01 (503.23 *31) CORRECT

      2. Date Filter=2/1/22 to 2/28/22, MMP_Tons/Day = 524.45, MMP_Tons = 14,684.59 (524.45 * 28) CORRECT

      3. Date Filter = 1/1/22 to 2/1/22, MMP_Tons = 32,885.62 (1027.68 * 32) INCORRECT

      CORRECT VALUE = 16,124.58 = (503.23 * 31) + (524.45 * 1)

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        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!