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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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