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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Budget treatas

Hello!

 

I have 2 tables ( one has all the sales info) and the other has de budget allocation for each salesperson by year and month for the last 3 years ( 2017, 2018 and 2019)

 

How can I calculate the budget for each sales person for each year and month?

 

I have to allocate by salesperson, by year and by month, but treatas only let's me allocate once.

How could I create a measure for total budget that works for this?

 

Total Budget = CALCULATE(SUM(OBJECTIVES[OBJECTIVO]);
                                 TREATAS(values(Dates[Year]);OBJECTIVES[year]) ;
                                  TREATAS(VALUES(Dates[MonthOfYear]);OBJECTIVES[MÊS]))
 
Thanks
1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Change the data type to whole number in data pane as the picture below.

whole.PNG

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

8 REPLIES 8
v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

thank s for the help so I can allocate budget by year and by month

Hi @Anonymous,

 

1. To create a calculated column in BUDGET table.

 

YearMonth = BUDGET[YEAR]*10000+'BUDGET'[MONTH]*100

2. Create a measure as below.

 

TOTAL BUDGET1 = CALCULATE(SUM(BUDGET[OBJECTIVE]), TREATAS(VALUES('DATES'[MonthnYear]),'BUDGET'[YearMonth]))

Capture.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @Anonymous ,

 

Change the data type to whole number in data pane as the picture below.

whole.PNG

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Thanks 🙂  !

 

Have a nice day!

 

 

Anonymous
Not applicable

Hi Frank  !

 

Because the month value is text, I can't multiply by 100.

 

Is there any way else?

 

Thanks

 

Telmo

Anonymous
Not applicable

Hi!

 

Because the month value is text, I can't multiply by 100.

 

Is there any way else?

 

Thanks

 

Telmo

Anonymous
Not applicable

TOTAL BUDGET doesn't iterates by month, so the measure total budget = cumulative budget.

I have sent u the excel file and PBI file.

And budget sucess is exactly the same for each month wich is wrong.

 

Thank u!

 

https://1drv.ms/f/s!AuSuG7Ypxe7xiUPp2MM28cFgJtfj

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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