Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
troyhimes
Resolver I
Resolver I

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'

troyhimes_6-1645405619445.png

 

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.

troyhimes_7-1645405716662.png

 

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!

 

 

 

1 ACCEPTED SOLUTION

@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
1.png

 

 

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!

View solution in original post

8 REPLIES 8
tamerj1
Super User
Super User

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

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)

 

@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
1.png

 

 

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!

Thanks @tamerj1 , much appreciated, that did the trick.

amitchandak
Super User
Super User

@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-...

 

 

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...

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

troyhimes_0-1645421678319.png

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(?)

@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

@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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.